Re: VBA Recordset Query String Length Limit

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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" wrote:

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" <JeffBanning@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8AAD94F5-517E-4AA9-ADD8-372471B74E32@xxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: Self-Documenting Code Contest
    ... self-documenting. ... query:= 'documenting' asSortedCollection. ... string size < query size ... two words becomes a set of Characters. ...
    (comp.lang.smalltalk)
  • Re: formatting numbers after using the nz function
    ... Especially since Access can treat a string of number characters as a number in many cases. ... "John Spencer" wrote: ... I can format the resulting numbers in the query and in a report as standsrd with two decimal places. ...
    (microsoft.public.access.queries)
  • Re: Query returns no data
    ... I have constructed my query to direct to this database with the search field "subject" in ascending order. ... Compares a string expression to a pattern in an SQL expression. ... Multiple characters a*a aa, aBa, aBBBa aBC ...
    (microsoft.public.access.gettingstarted)
  • Re: Update SET ?
    ... This isn't an issue with building your query. ... Employees and take that to mean the end of your string. ... characters that appear after it will just cause confusion, ... > Taking data from a form, and using UPDATE tablename SET ...
    (microsoft.public.inetserver.asp.db)
  • Re: Incremental search with numbers
    ... If you mean that you don't know how to enter that in the graphical query designer, the easiest way is to use the View menu and change to SQL view, where you can directly edit the sql statement. ... > my program places it in a SQL query which uses a LIKE 'A*' to extract> all ... >>> characters keyed in on my form. ...
    (microsoft.public.access.formscoding)