SQL variable string length limit?

A

Al

Hi,
I hope someone can help me with this because I cannot see what I'm doing
wrong. I am using a variable (txtSinList) to hold a string that is used
inside a pass-thru query. The string is quite long and listed below. When I
step through the code, I see that it is only seeing part of the string and
concatenating it right after Copper. I have included the partial string
contained in the variable below also.

Original string:
txtSinList = "Analyte = 'Aluminum, Total' or Analyte = 'Antimony, Total'or
Analyte = 'Arsenic, Total'or Analyte = 'Barium, Total'or Analyte =
'Beryllium, Total'" & _
"or Analyte = 'Cadmium, Total' or Analyte = 'Calcium, Total' or Analyte =
'Chromium, Total'" & _
"or Analyte = 'Copper, Total' or Analyte = 'Cyanide, Total' or Analyte =
'Iron, Total' or Analyte = 'Lead, Total' or Analyte = 'Magnesium, Total'" & _
"or Analyte = 'Manganese, Total' or Analyte = 'Mercury, Total' or Analyte =
'Nickel, Total' or Analyte = 'Potassium, Total' or Analyte = 'Selenium,
Total'" & _
"or Analyte = 'Silicon, Total' or Analyte = 'Silver, Total' or Analyte =
'Sodium, Total' or Analyte = 'Thallium, Total' or Analyte = 'Zinc, Total'"

What is contained in the txtSinList variable at runtime:
"Analyte = 'Aluminum, Total' or Analyte = 'Antimony, Total'or Analyte =
'Arsenic, Total'or Analyte = 'Barium, Total'or Analyte = 'Beryllium, Total'or
Analyte = 'Cadmium, Total' or Analyte = 'Calcium, Total' or Analyte =
'Chromium, Total'or Analyte = 'Cop"

Here is the SQL statement that works but only brings up some of the
requested analytes listed:
strSQL = "SELECT * FROM Customer WHERE StartDate >= '" & C1 & "' and
StartDate <= '" & C2 & "' and (ClientID = '" & txtClList & "') and (" &
txtSinList & ") and (sampname <> 'Finished') order by sampname, Analyte,
Ordno asc;"

Is there a limit to the string length that can be used inside a variable
like this?
I hope this makes sense. Thank you for your help.
Al
 
C

Chris

I found this in the VBA Help:
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.

So the length of your SQL statement shouldn't be an issue. That being said,
it would be easier to store the values you are storing in txtSinList in a
table and reference the table in your query. This would also be much easier
to maintain. Then your query would look something like:
strSQL = "SELECT * FROM Customer WHERE StartDate >= '" & C1 & "' and
StartDate <= '" & C2 & "' and (ClientID = '" & txtClList & "') and (Analyte
In(Select <<your field name>> From <<your table name here>>;) and (sampname
<> 'Finished') order by sampname, Analyte,
Ordno asc;"
 
J

J_Goddard via AccessMonster.com

Hi -

How are you determining what is in the string at runtime (e.g. mouseover or
debug.print) ? If the runtime contents of txtSinList are as you have shown,
it would generate an SQL syntax error due to unbalanced quotes. Is that what
is happening?

John
 
A

Al

Hi Al,
I agree with Chris, however, I am surprised that your statment worked
without giving you a type mismatch error on said:
StartDate <= '" & C2 & "'>. This should be:
Click to expand...

Where StartDate >=#" & C1 & "# and StartDate <=#" & C2 & "#

using the apostrophe is for text string. I am surprised that the code did
not break for a type mismatch unless you have your fields formatted as text
not dates.
Al
 
A

Al

I am getting the variable's value from the "Locals" window at runtime. I
click on the variable, then on the value it contains, and I copy it out of
there to look at.

As for the date question posed by Al, they have always worked fine. This
application has been running for 7 or 8 years now this way. Someone has just
requested a custom query embedded into this code (a copy of the original) for
a special report that they need to run on a regular basis.

Thanks,
Al
 
A

Al

I have never been able to get a reference to "In(" to work with Access
before. When I tried it this time, it gives the error: "Compile error:
Expected: )"

Here is the statement modified to look at a table with the needed analytes:
strSQL = "SELECT * FROM Customer WHERE StartDate >= '" & C1 & "' and
StartDate <= '" & C2 & "' and (ClientID = '" & txtClList & "') & _
and (Analyte In(Select DMRANA From DMRAnalytes;)) and (sampname <>
'Finished') order by sampname, Analyte, Ordno asc;"

Thanks,
Al
 
D

Dirk Goldgar

Al said:
I have never been able to get a reference to "In(" to work with Access
before. When I tried it this time, it gives the error: "Compile error:
Expected: )"

Here is the statement modified to look at a table with the needed
analytes:
strSQL = "SELECT * FROM Customer WHERE StartDate >= '" & C1 & "' and
StartDate <= '" & C2 & "' and (ClientID = '" & txtClList & "') & _
and (Analyte In(Select DMRANA From DMRAnalytes;)) and (sampname <>
'Finished') order by sampname, Analyte, Ordno asc;"
Click to expand...


You've got an unwanted semicolon in your subquery and, I think, some missing
quotes. Try this:

strSQL = _
"SELECT * FROM Customer WHERE " & _
"StartDate >= #" & C1 & "# And " & _
"StartDate <= #" & C2 & "# And " & _
"(ClientID = '" & txtClList & "') And " & _
"(Analyte In(Select DMRANA From DMRAnalytes)) And " & _
"(sampname <> 'Finished') " & _
"ORDER BY sampname, Analyte, Ordno;"

I make no guarantees that the above is completely correct, as I haven't
tested it. Note that the validity of your date criteria is going to depend
on what is in C1 and C2. They must be unambiguous date values, or else be
in the MM/DD/YYYY format.
 
D

Dirk Goldgar

Al said:
I am getting the variable's value from the "Locals" window at runtime. I
click on the variable, then on the value it contains, and I copy it out of
there to look at.
Click to expand...

I think the Locals window only displays the first part (255 characters,
maybe?) of very long strings. You could use the Immediate window to check
on the full string:

?Me!txtSinList

?strSQL
 
J

J_Goddard via AccessMonster.com

Try using debug.print to see the contents of your long string. The locals
window value column (from what I can see) only gives you the first 253
characters; debug.print prints the whole thing.

John

I am getting the variable's value from the "Locals" window at runtime. I
click on the variable, then on the value it contains, and I copy it out of
there to look at.

As for the date question posed by Al, they have always worked fine. This
application has been running for 7 or 8 years now this way. Someone has just
requested a custom query embedded into this code (a copy of the original) for
a special report that they need to run on a regular basis.

Thanks,
Al
Hi -
Click to expand...
[quoted text clipped - 44 lines]
Click to expand...
 
Top