SQL query length restirction

[Lance]

hi all,
I am writing query statement which needs to be executed. for some reason
I can't define query string length greater than 250 letters. Is there a
way around this??. I have defined the sql statement as string.
thanks,

 

[Van T. Dinh]

An SQL String can be up to 64K characters.

Describe how you "define query".

HTH
Van T. Dinh
MVP (Access)

 

[Ben]

I think he means when he uses a macro to execute the SQL statement, there is
a 256 character limit.

I also need to know how to get around this?

 

[Lance]

Example of code which I am using

Dim strSQL As String

strSQL = "SELECT qryCETESBWellStats.Year FROM qryCETESBWellStats GROUP
BY qryCETESBWellStats.Year, qryCETESBWellStats.Analyte,
qryCETESBWellStats.Aquifier, qryCETESBWellStats.ZoneClass HAVING
(((qryCETESBWellStats.Analyte)= '" & Me.cbAnalyte & "') AND
((qryCETESBWellStats.Aquifier)= '" & Me.cmbAquifier & "') AND
((qryCETESBWellStats.ZoneClass)='" & Me.cmbZone & "'));"


Me.cmbYear.RowSource = strSQL
Me.cmbYear.Requery
cmbYear = cmbYear.ItemData(0)

This is What I get in debug mode for strSQL

"SELECT qryCETESBWellStats.Year FROM qryCETESBWellStats GROUP BY
qryCETESBWellStats.Year, qryCETESBWellStats.Analyte,
qryCETESBWellStats.Aquifier, qryCETESBWellStats.ZoneClass HAVING
(((qryCETESBWellStats.Analyte)= '1,1,1,2-Tetrachloroethane') AND ((qy"
<---- anything above that is missing

 

[Douglas J. Steele]

I've used SQL statements that are much longer than that,but I don't write
them as a single row.

See whether the following works:

strSQL = "SELECT qryCETESBWellStats.Year FROM qryCETESBWellStats " & _
"GROUP BY qryCETESBWellStats.Year, qryCETESBWellStats.Analyte, " & _
"qryCETESBWellStats.Aquifier, qryCETESBWellStats.ZoneClass HAVING " & _
"(((qryCETESBWellStats.Analyte)= '" & Me.cbAnalyte & "') AND " & _
"((qryCETESBWellStats.Aquifier)= '" & Me.cmbAquifier & "') AND " & _
"((qryCETESBWellStats.ZoneClass)='" & Me.cmbZone & "'))"

or

strSQL = "SELECT qryCETESBWellStats.Year FROM qryCETESBWellStats "
strSQL = strSQL & "GROUP BY qryCETESBWellStats.Year,
qryCETESBWellStats.Analyte, "
strSQL = strSQL & "qryCETESBWellStats.Aquifier, qryCETESBWellStats.ZoneClass
HAVING "
strSQL = strSQL & "(((qryCETESBWellStats.Analyte)= '" & Me.cbAnalyte & "')
AND "
strSQL = strSQL & "((qryCETESBWellStats.Aquifier)= '" & Me.cmbAquifier & "')
AND "
strSQL = strSQL & "((qryCETESBWellStats.ZoneClass)='" & Me.cmbZone & "'))"

BTW, Year is a reserved word, so really isn't a good choice for a field
name. If you absolutely can't change it, you may want to put square brackets
around it:

strSQL = "SELECT qryCETESBWellStats.[Year] FROM qryCETESBWellStats ....

 

[Lance]

I have tried that its not working
maybe I am doing some mistake. I will recheck. Thanks for your advise on
the Year function.