Search SQL String is being shortened

[Guest]

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:

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)"

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 cannot figure why it is cutting off the last bit. Does anyone maybe know
where I missed something perhaps?

Thanks for your attention!

regards,
jean

 

[Squirrel]

Hi Jean,

I come here to learn and don't know the answer to your question but note
that the string length
is - I think - 256 chars before truncation which seems a very significant
number. I think you've
run into some string length barrier here.

-Linda

 

[Guest]

Hi Linda,

Yes I am also pretty much a learner, and I also suspected the issue which
you suggested. I checked the amount of characters for the SQL string in MS
Word and it was 253 or something close.

Anyone else, how do I overcome this limitation?

I tried declaring strWhere as follows:
--------
Dim strWhere as String * 10000

--------

This only seems to declare a string with fixed length, and once I add
something to it e.g.

strWhere = "WHERE"

then i cannot add something again later...it just stays as "WHERE"
Aaaaaa!

Can someone please help?

 

[Guest]

Hey me again...

Seems you were absolutely right Linda.
I have to go and rewrite my code, and make sure that strWhere does not get
too big.

Thanks!

 

[Dan Artuso]

Hi,
String variables can hold quite a bit. For all practical purposes there is no length restriction and
certainly not 256 characters. I can stuff a 2MB text file into a string variable easily.

Are you sure that your code is not truncating it somewhere?

 

[Guest]

Hi Dan,

No, I'm actually not 100% sure about the truncation part. Does it mean that
I have to check if somewhere along the line I have some chararcters of the
string deleted?

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 part which is labeled 'Remove the last AND from the SQL statment,
I think does not truncate it incorrectly, since the value of strWhere is
then already shorter than it should be.

Is there also a limit to the value which is passed to the property RowSource
of a list box? (See last line of code). I get run-time error 2176 "The
setting for this property is too long"

?

 

[Gijs Beukenoot]

Het is zò dat bavjean formuleerde :

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

Try this instead of the "strWhere = Mid(strWhere, 1, Len(strWhere) -
5)"

strWhere = ""
debug.print strWhere
debug.print strGeneral
debug.print strAnthroMain
debug.print strAnthroSub

strWhere = "WHERE " & trim(strgeneral) & " " & Trim(strGeneral) & " "
& Trim(strAnthroMain) & " " & (Trim(strAnthroSub)
strWhere = Right( strWhere, Len( strWhere ) - 3 ) 'cutting off the
last AND
debug.print strWhere

 

[Bruce M. Thompson]

When dimming your string variables, do not dim them as fixed sizes.

Instead of:
Dim strWhere as String * 10000
And
Dim strAnthroSub As String * 5000

Use:
Dim strWhere as String
And
Dim strAnthroSub As String

Setting the string variable's length causes the variable's value to be padded to
the declared length when you assign a string value that is shorter than that
declared length.

 

[Guest]

Thanks Gijs,

I tried your suggestion, and the SQL string is concatenated correctly,
except that it cuts off the „WHE“ of „WHERE“ at the beginnig of the string.
This is not such a major issue though.

The main issue now is, when I pass this string strWhere to the RowSource of
a list box, I get a run-time error 2176 „The setting for this property is too
long". This only happens at run-time, and the project compiles fine
otherwise.

I have a form of which the user can choose ranges as criteria for a search,
and this means that the WHERE part of my SQL string can get quite long. I
suspect that there is a limit to this in some or other way, maybe in the size
of the WHERE part of the string I pass to the RowSource.

Should I keep trying to find a way out, or should I adopt another method for
processing the criteria for the WHERE string (strWhere)?

Thanks once again to everyone that has contributed to this thread so far, it
has really been kind of you.

Regards,

Jean

 

[Gijs Beukenoot]

bavjean formuleerde op dinsdag :

Thanks Gijs,

I tried your suggestion, and the SQL string is concatenated correctly,
except that it cuts off the ⤸WHE⤽ of ⤸WHERE⤽ at the beginnig of the
string. This is not such a major issue though.

Hmm, sorry, should't be Right( .... but Left( ....

The main issue now is, when I pass this string strWhere to the RowSource of
a list box, I get a run-time error 2176 ⤸The setting for this property is
too long". This only happens at run-time, and the project compiles fine
otherwise.

Ah. To my knowledge, this can be a maximum of 2048 characters...

I have a form of which the user can choose ranges as criteria for a search,
and this means that the WHERE part of my SQL string can get quite long. I
suspect that there is a limit to this in some or other way, maybe in the size
of the WHERE part of the string I pass to the RowSource.

Should I keep trying to find a way out, or should I adopt another method for
processing the criteria for the WHERE string (strWhere)?

You can try the following:
In your query, you might have fields from different tables. Most of
these will be prefixed by the tablename (tblCustomers.Address).
One way of shortening your SQL is to alias the tables:
SELECT A.Address, B.Price FROM tblCustomers A, tblProducts B
This can shorten your SQL _if_ you have many of these tblCustomers,
tblProdutcs, etc.

Another solution could be to create a (temporary) query for it :

dim qdTemp as dao.querydef

set qdTemp = currentdb.querydefs("qryTemp")
qdTemp.SQL = <your completer SQL statement>

Then, set the recordsource for the control/form/report you open to that
qryTemp.

 

[Guest]

Hi Gijs,

I rewrote the SQL and it works fine...for now. The 2048 character limit is
creeping up on me though, and will have to be dealt with sooner or later.

I based this query in my code on another query, and not a table(s). But
substituting the query name with a single character will save a lot of space
indeed! Nice tip, I remember now seeing it somewhere while learning SQL
syntax long ago!

If this does not suffice in the end, I will also try your suggestion with
the temp query. Thanks very much once again for your help.

 

[Gijs Beukenoot]

bavjean bracht volgend idée uit :

Hi Gijs,

I rewrote the SQL and it works fine...for now. The 2048 character limit is
creeping up on me though, and will have to be dealt with sooner or later.

I based this query in my code on another query, and not a table(s). But
substituting the query name with a single character will save a lot of space
indeed! Nice tip, I remember now seeing it somewhere while learning SQL
syntax long ago!

If this does not suffice in the end, I will also try your suggestion with
the temp query. Thanks very much once again for your help.

You're welcome