Search SQL String being shortened

antifashionpimp · Nov 26, 2004

Hi all,

I have an event handler on the cmdOK button of a search form. I declare a string variable, strWhere, which represents the WHERE part of the SQL syntax used for searching.

I then call 2 functions that return strings and add them together to strWhere, as follows:

Code:

strWhere = strWhere & SearchAnthroMain(Me, intSex, strSTLProp, strVFG)
strWhere = strWhere & SearchAnthroSub(Forms!frmPopUp_ZusätzVermessungen)

the first call(SearchAnthroMain) returns the following string:
"WHERE (qryAlleVPmEndwerte.Körperhöhe) >= 1740 AND (qryAlleVPmEndwerte.Körperhöhe) <= 1760 AND ((qryAlleVPmEndwerte.Alter) BETWEEN 20 AND 40) AND (qryAlleVPmEndwerte.VFG) = True AND"

and the second functon call(SearchAnthroSub) returns the following string:
" ((qryAlleVPmEndwerte.Beckenbreite) BETWEEN 320 AND 325) AND"

Fine, that is all correct, however, when adding the two together, it cuts off the last part of the second string, as follows(I checked this using debug.print for strWhere, and checking the value of the variable strWhere in the locals window of VBE)

"WHERE (qryAlleVPmEndwerte.Sex) = 'm' AND (qryAlleVPmEndwerte.Körperhöhe) >= 1740 AND (qryAlleVPmEndwerte.Körperhöhe) <= 1760 AND ((qryAlleVPmEndwerte.Alter) BETWEEN 20 AND 40) AND (qryAlleVPmEndwerte.VFG) = True AND ((qryAlleVPmEndwerte.Beckenbreite)"

Therefore, the following gets cut off:
BETWEEN 320 AND 325) AND

Why does it cut off the last part? Is the string data type too small? I found this on MS’s description of the string data type for VBA:
„There are two kinds of strings: variable-length and fixed-length strings.
· A variable-length string can contain up to approximately 2 billion (2^31) characters.
· A fixed-length string can contain 1 to approximately 64K (2^16) characters.
Note Public fixed-length string can't be used in a class module.“

I still cannot figure why it is cutting off the last bit though. Does anyone maybe know where I missed something perhaps?

Thanks for your attention!

regards,
jean

antifashionpimp · Nov 26, 2004

I searched a few posts on "string limit" and this has told me to go and rewrite my code. Apparently 256 characters is the limit. Is this the limit then for a string passed to an SQL query, and MS's definition is for a general string variable?

The thread that showed me the light

antifashionpimp · Nov 26, 2004

Doh!

Im still having problems, I rewrote my code a few times, and it looks like this now:

Here is my code again...after rewriting it a few times.

Code:

---------------------------------------------
Private Sub cmdSearch_Click()

'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String
Dim strWhere As String * 10000

Dim strGeneral As String
Dim strAnthroMain As String
Dim strAnthroSub As String * 5000

Dim strErgebnisse As String
Dim intErgebnisse As Integer

'variables to store values from form

'Dim dblKPH As Double, dblKPHAbw As Double
Dim intSex As Integer 'for convert functions (Körperhöhe <--> Perzentil)
Dim strSex As String 'for SQL
Dim strSTLProp As String
Dim strVFG As String

Select Case Me.cboSex
  Case "Man"
    intSex = 1
    strSex = "m"
  Case "Woman"
    intSex = 2
    strSex = "w"
End Select
Select Case Nz(Me.cboSTL_KPH)
  Case "SZ"
    strSTLProp = "SZ"
  Case "MM"
    strSTLProp = "MM"
  Case "SR"
    strSTLProp = "SR"
End Select

strVFG = Me.cboVFG
    'Constant Select statement for the RowSource (i.e. column headings)
    strSQL = "SELECT qryAlleVPmEndwerte.VPNummer, 
qryAlleVPmEndwerte.Nachname, qryAlleVPmEndwerte.Vorname, " & _
                "qryAlleVPmEndwerte.Abteilung, qryAlleVPmEndwerte.Telefon, 
qryAlleVPmEndwerte.GebJahr, qryAlleVPmEndwerte.Alter, " & _
                 "qryAlleVPmEndwerte.Körperhöhe, 
qryAlleVPmEndwerte.StammlProp, qryAlleVPmEndwerte.Stammlänge,  " & _
                  "qryAlleVPmEndwerte.BMI, qryAlleVPmEndwerte.VFG " & _
             "FROM qryAlleVPmEndwerte"
    
    'strWhere = "WHERE"
    
    strOrder = " ORDER BY qryAlleVPmEndwerte.VPNummer;"
    
    'insert sex
    strGeneral = " (qryAlleVPmEndwerte.Sex) = '" & strSex & "' AND "
    
    'Search criteria for anthropometric-specific data
    '------------------------------------------------
    'Main criteria e.g. Körperhöhe, Stammlänge, BMI etc.
    strAnthroMain = SearchAnthroMain(Me, intSex, strSTLProp, strVFG)
    'Other criteria e.g Beckenbreite, Fußhöhe, Oberarmumfang etc.
    strAnthroSub = SearchAnthroSub(Forms!frmPopUp_ZusätzVermessungen)


    strWhere = "WHERE" & strGeneral & strAnthroMain & strAnthroSub

    'Remove the last AND from the SQL statment
    strWhere = Mid(strWhere, 1, Len(strWhere) - 5)
    
    'Pass the SQL to the RowSource of the listbox
    Me.lstErgebnisse.RowSource = strSQL & " " & strWhere & " " & strOrder

The problem comes into play when the very last line of code executes, I get a run-time error 2176 "The setting for this property is too long"
Is there a limit to what kind of string I can pass to RowSource?
And what can I do to solve this?

I kindly ask you for any help...

Search SQL String being shortened

antifashionpimp

Registered User.

antifashionpimp

Registered User.

antifashionpimp

Registered User.

Similar threads

Users who are viewing this thread