Syntax Error (Missing Operator) In Query Expression

[afreymor]

Hi all,

I have created a form based on a query. The funny thing is when I tried to sort fields on the form, the following message box pops up:

Syntax Error (Missing Operator) In (Field Name)

I just did the exact same thing several weeks ago, and that first form could sort fields normally. The only difference between the first one and this is that this second query was based on several tables, while the first was based on a single table, although I doubt that is where the problem is.

Any help would be appreciated!

EDIT: I forgot to add that I could sort fields where the field name doesn't have spaces in it. For example, the field name "Customer ID" triggers the syntax error, while the field "S/N", "Company", etc. can be sorted like normal.

 

[pr2-eugin]

This is a very good example of Why you should follow a good naming convention.

Make sure the field is made to escapes special characters by enclosing them in SQUARE brackets.

 

[afreymor]

Thank you for your reply pr2,

Yes, I became aware of my naming problems later on in the project, but I do not think this is the problem. Like I mentioned the first query which I made several weeks prior works fine, and it uses the same name formats.

Yes, in my codes the field names with spaces have square brackets.

I could go back and rename all fields but I am afraid it will mess up the codes, as those do not update itself. I would like to look for alternatives first and save that as a last resort.

 

[pr2-eugin]

Could you show the complete Query? I guess there is some JOIN?

Other way to go around is creating custom Sort/Filters.

 

[afreymor]

Do you mean the SQL code? Sorry if I misunderstood.

----------------------------------------------------------------------------------

SELECT [qry Sales Details].[Date Sold], [qry Sales Details].[Sale ID], [qry Sales Details].[Customer ID], [qry Sales Details].[First Name], [qry Sales Details].Company, [qry Sales Details].[Machine Type], [qry Sales Details].Model, [qry Sales Details].[S/N], [qry Sales Details].[Payment Status]

FROM [qry Sales Details]

WHERE ((([Date Sold] Like "*" & [Forms]![searchfrm SalesDetails]![txtDateSold] & "*" Or [Forms]![searchfrm SalesDetails]![txtDateSold] Is Null)=True) AND (([First Name] Like "*" & [Forms]![searchfrm SalesDetails]![txtFirstName] & "*" Or [Forms]![searchfrm SalesDetails]![txtFirstName] Is Null)=True) AND (([Company] Like "*" & [Forms]![searchfrm SalesDetails]![txtCompany] & "*" Or [Forms]![searchfrm SalesDetails]![txtCompany] Is Null)=True) AND (([Payment Status] Like "*" & [Forms]![searchfrm SalesDetails]![ComboBoxPaymentStatus] & "*" Or [Forms]![searchfrm SalesDetails]![ComboBoxPaymentStatus] Is Null)=True));

------------------------------------------------------------------------------------

I did not use JOIN; instead I made a query and based this second query off it. Again, the problem arises only when I tried to sort the fields on the form...

Thanks again for having a look at my work

 

[afreymor]

Ok, something strange just happened. I decided to use redo the form (using the form wizard again, as I did with my previous two forms) just to see where I could have went wrong, and this time, everything works. I can sort every single column now

Most probably I did something wrong with the form and/or field properties after I created it.

Sorry for the trouble!

 

[pr2-eugin]

Not a problem at all. Glad you have it sorted now.

 

[Hagridore]

This is a very good example of Why you should follow a good naming convention.

Make sure the field is made to escape special characters by enclosing them in SQUARE brackets.

In my database there is one value that requires the use of a /. (This is not as a name of anything...just a value stored in one of the fields.) I have a form which functions beautifully in all other regards, but it produces the Incorrect Syntax in Expression error if I try to use this value as a criteria for a search/filter. I've tried using double quotes and square brackets around the / and a \ in front of it to no avail. I don't have the option of changing the value...it is defined by this multi-billion dollar project. Can anyone help?