SQL string is being shortened

[antifashionpimp]

Hello,

I orginally posted this message under "Forms", but it isn't getting the feedback I was hoping for. Maybe it belongs here then.

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:

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

---------------------------------------------------

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.

---------------------------------------------
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...

 

[Pat Hartman]

Please don't make multiple posts on the same topic.
http://www.access-programmers.co.uk/forums/showthread.php?t=77218