Syntax error only shows when loading report

[Reginald]

Hello,
I have a form with a list area and a button to load a report.


Both of these use the same query and the result shows up in the list area fine, but when I click the button to load the report, it throws an error (3075) saying I have an error in the syntax.
The query is:

"SELECT ID,description,location FROM assets WHERE  assets.sublocation like '*" & sublocation & "*'" & " And assets.[Date Removed] Is Null"

Can anyone help?
-Reginald

[CJ_London]

description is a reserved word - as a minimum, put it in square brackets but ideally change to something more meaningful (like assetDescription) - and get rid of spaces in your field names whilst you are at it -potentially another reason for unexplained errors

if this does not solve the problem, I can't see anything wrong with your sql except you can simplify

....& sublocation & "*'" & " And assets....

to

....& sublocation & "*' And assets....

So the only other thing is your data - do you have sublocations with single quotes in them? e.g. "St. Jame's Place"

if so use the replace function to replace single quotes with two single quotes

....& replace(sublocation,"'","''") & "*' And assets....

[Reginald]

Thanks for your quick response,
Why does the query work for populating the listbox?
I have no single quotes in any of my sublocations or in any other data as well.
I tried to run with the square brackets around description but no joy.
Also ran the query

"SELECT ID,[description],location FROM assets WHERE  assets.sublocation like '*" & sublocation & "*'"

to remove Date Removed and still the same error shows.

[CJ_London]

I've provided my best guess on the information provided so what exactly does your syntax error say?

and have you used debug.print to generate the actual sql string and then copy pasted to the query sql window to see what errors it generates

[Reginald]

This is a screenshot of the error

[CJ_London]

can you view it in the query grid - any clues there?

try building a new query in the query grid and see how the sql compares

otherwise, just leaves the report - what exactly are you doing there? perhaps sublocation does not exist in the report or has not been assigned a value

[Reginald]

I'm afraid I don't know what a query grid is.
Also sublocation in query has the value

ADO Kitchen

Also I can't understand why the query works fine for populating the listbox?

[rpeare]

Take out the asset. in your SQL statement, you're only referencing one table so it's unnecessary.

see if this works
select id, description, location from assets where sublocation like '*ado kitch*' and [date removed] is null

it did for me, if it does then start substituting in your variables until it breaks and tell us where it bombs out.

[CJ_London]

query grid - the form that opens when you open a new or existing query

if it works fine for populating the listbox, then as previous suggested this implies an issue with your report

[rpeare]

ohhhhh.... snap.... I misread... again... if you're opening a report using a field from a form as a criteria your syntax would be


"SELECT ID,description,location FROM assets WHERE assets.sublocation like '*" & forms!FORMNAME!sublocation & "*'" & " And assets.[Date Removed] Is Null"

[Reginald]

ohhhhh.... snap.... I misread... again... if you're opening a report using a field from a form as a criteria your syntax would be


"SELECT ID,description,location FROM assets WHERE assets.sublocation like '*" & forms!FORMNAME!sublocation & "*'" & " And assets.[Date Removed] Is Null"

Thanks but it's not necessary to use forms!Formname since sublocation is defined as follows:
Dim sublocation As String

    If IsNull(cmbxSubLocation) = False Then        bSubLocation = True
        'sublocation = Me.cmbxSubLocation.Column(1)
         sublocation = Replace(Me.cmbxSubLocation.Column(1), "'", "''")
    Else
        bSubLocation = False
        
    End If

And it still works to populate the listbox.
I'm thinking it has nothing to do with the query...
Reginald

[rpeare]

How about uploading a sample database, I'm pretty sure it's the way you're referencing the sublocation but I'm not making myself clear.

[hapm]

When and where do you assign your query to the reports datasource? Perhaps its easier to use the DoCmd.OpenReport method in combination with the WhereCondition parameter.

[Reginald]

Well,
I got it working.
I had to remove the WHERE word in the condition so that it looks like this:

"sublocation like '*" & sublocation & "*'" & " And [Date Removed] Is Null"

instead of this:

"SELECT ID,[description],location FROM assets WHERE  sublocation like '*" & sublocation & "*'" & " And [Date Removed] Is Null"

So I guess that takes care of it then.
Thanks everybody for your time and help.

[Reginald]

Thanks hapm,
Your post set me on the right track.
Do you know how to mark post as

Solved

?