VBA Recordset Query String Length Limit

[Jeff Banning]

I have run into a problem trying to set a report recordset from a form.
Basically, you can select multiple items into a listbox which builds a query
string:

select * from vwData where Thing='List1' or Thing='List2' or Thing='List3'
......

I then drop that string into this bit of code to set the report recordset:
Set rstData = CurrentDb.OpenRecordset(strSQL)
DoCmd.OpenReport "rptData", acViewPreview
rstData.Close
Set rstData = Nothing

The problem is that once the SQL query string gets above 256 characters, the
report bombs because it looks like the recordset cuts off the string at 256.
If I keep it under 256, it works as expected.

I could not find anything referencing this issue on my initial searches, so
I am hoping someone here might be able to help me with a fix, workaround or
whatever.

THanks,
Jeff

 

[Alex Dybenko]

Hi,
what means report bombs? what error do you get?
then, I think, you can use easy approach - just set resulting SQL to report
recordsource property, or to a query SQL, which is recordsource.

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Jeff Banning]

When the string that is used as the SQL query is greater than 256 characters
Access gives me this error:

Run-time error '3075':
Syntax error in string in query expression 'Thing='List1' Or Thing='List2'
Or ....

I can see that the end of the query string displayed in the error message is
cut off at the 256 character location. As best as I can tell, Access cannot
handle a SQL query string that is longer than 256 characters. At least for
this particular method.

And I am afraid you will have to be a little more specific in your
suggestion because I am not understanding what you mean in your suggested fix.

Thanks for your help.

Jeff

 

[Alex Dybenko]

Hi,
create a new query, say qryRptData, set your report rptData recordsouce
property to this query and change your code as:

currentdb.querydefs("qryRptData").SQL=strSQL
DoCmd.OpenReport "rptData", acViewPreview

so this will set qryRptData query SQL to filtered expression and you will
have your report filtered also

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Douglas J. Steele]

Note, too, that there very well could be ways of making your SQL string
shorter.

For instance, rather than Thing='List1' or Thing='List2' or Thing='List3',
use Thing In ('List1', 'List2', 'List3')

 

[Dirk Goldgar]

In

I have run into a problem trying to set a report recordset from a
form. Basically, you can select multiple items into a listbox which
builds a query string:

select * from vwData where Thing='List1' or Thing='List2' or
Thing='List3' .....

I then drop that string into this bit of code to set the report
recordset:
Set rstData = CurrentDb.OpenRecordset(strSQL)
DoCmd.OpenReport "rptData", acViewPreview
rstData.Close
Set rstData = Nothing

What puzzles me is that the code you posted doesn't do anything to set
the report's recordset. Is that a global recordset, and do you have
code in the report's Open event to set its recordset to rstData? Maybe
if you posted the code that actually sets the report's recordset or
recordsource, we could see what the limiting factor is.

 

[Jeff Banning]

Thanks for everyone's help. I tried Alex's suggestion and it worked great.

Instead of creating a recordset and then pointing to the recordset on the
report open event, I am changing an existing query's SQL string that is the
report's current recordsource.

Sometimes I wonder what the heck I was thinking when I set these things up.

Thanks again,
Jeff