none
Apply filter to query results colum = Syntax error (missing operator) in query expression 'xxx'

    Question

  • Upon creating a new query (before saving it) and then applying a auto filter, I always get error "Syntax error (missing operator) in query expression 'xxx'" where xxx is the name of the column.

    After dismissing the error, I can apply a Text Filter, which works fine but I am not presented with the Auto Filter list box of items.

    Once I save the query however, the Auto Filter populates with the values. Sometimes it is necessary to run a quick query and find a few items without actually saving the query afterward.

    I do not have this problem on my Home PC, only my office XP SP3 laptop.

    Thanks

    Andre

    Wednesday, January 19, 2011 5:27 PM

All replies

  •  Hi AndreUys,

    Thank you for posting and we are glad to help with you.

    After reading your post, I found it difficult to reproduce your issue, so would you mind uploading your file to the SkyDrive:http://www.windowslive.co.uk/skydrive , so that I can do further research about your issue. By the way, what's the version of your Access.

    I can't give you the exact reason because of I can't reproduce your issue, but I can give you some suggestions:

    1. Try to repair your Office from the Control Panel.

    2. Please check whether the syntax is right, and such bellowing threads also encountered the error "Syntax Error (Missing Operator) in query expression" :

    http://bytes.com/topic/access/answers/207676-syntax-error-missing-operator-query-expression

    http://en.allexperts.com/q/Using-MS-Access-1440/2008/3/syntax-error-missing-operator-1.htm

    http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/fc888e1a-457e-41e8-b137-dc550874617f

    I hope these can help you and feel free to follow up after you have tried.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Proposed as answer by Bruce Song Friday, January 21, 2011 5:17 AM
    Friday, January 21, 2011 5:17 AM
  • Thanks for the info Bruce. I will try some of your recommendations.

    I am using Office 2010 and it was installed as part of the pro version. I also have Office 2010 Pro at home and just tried the following.

    When I open an accdb that I created at the office on my home PC and create a query, run it and apply a quick filter I get the error.

    If I open an accdb that I created on my home pc and create a query, run it and apply a quick filter, no error !

    I tried a compact an repair, but still the same problem.

    I will try your suggestions tomorrow and report back

     

    Thanks

    Friday, January 21, 2011 5:29 AM
  • Hi AndreUys,

    Have you resolved your issue? Does the suggestion work for you or not?

    Feel free to follow up after you have tried.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, January 24, 2011 9:52 AM
  • Bruce,

    I complete the repair from the control panel with no problem, then rebooted.

    I then created a new accdb on my desktop and imported a set of data from Excel.

    I however discovered the following. The data on which I applied the query, was based on an import from Excel. The data fields were named by Access according to the header row in Excel.

    The fields that would give me the above error were only the fields that had a space in the field name. E.g. “Country” field worked but field “Geographic Region” did not.

    When I edited the table and removed the space from “Geographic Region”, changing it to “Geographic_Region”, the syntax error would go away.

    I guess this might explain why my accdb's built at home (I never use spaces to define field names) did not produce the same error.

    Now, what is interesting is that once you save the query, then the filter works fine on the fields with spaces.

    Bug, maybe, but at least I now understand the reason for this.

    You have any suggestions ?

     

    Thanks

     

    Andre

     

    Monday, January 24, 2011 4:38 PM
  • Andre,

    That sounds like an actual Access bug there.

    Saving the Query SQL causes Access to do some extra evaulation of the query, and in doing so it may add the needed []s to the field name that has embedded spaces. You may have isloated a sequence of steps where that is not happening / hasn't happened _yet_.

    Could you build a set of scripted steps to reproduce that error? (a script of the steps where you can say this definitively: "if the column label in Excel has no spaces, these steps work as expected, but if there is a space in the column name, then when I do these staps, this happens and I get this error message...")


    Mark Burns, MCAD, MCP
    Sr. Microsoft Access Analyst/Developer
    Manager LinkedIn.Com community: Professional Microsoft Access Developers Network (PMADN)
    Monday, January 24, 2011 6:58 PM
  • Mark, I created an Excel File, Access DB and Word document to described this error in more detail.

    The link above points to a generic site, do I upload the files into my Skydrive ? How would I share it then. I dont want to share all my stuff with the world or people i dont know.

    Thanks

     

    Andre

    Thursday, February 03, 2011 5:07 AM
  • This issue is almost exactly a year old but I'm still experiencing it on Access 2010. Did it get fixed? If so, does anyone know the KB article?

    Thanks

    Martin

    Thursday, February 02, 2012 4:08 PM
  • Martin,

                Does not seem like this or any of the other many bugs have been fixed since I originally posted this.

    It has now just become second nature not to filter on new queries.

     

    Saturday, February 04, 2012 4:28 PM
  • Andre, you are my hero!  I was having the same issue and my situation was the same as yours.  I followed your steps to fix the Syntax Error and SUCCESS!  Thanks!

    Nicole

    Thursday, April 12, 2012 11:16 PM
  • Thanks Andre, the issue was on the output names, I noticed you don't need to change the filed name, by adding an Alias you solve the issue.

    form SQL --> SELECT [Last name] as last_name from table....

    or from desing view just add --> Last_name:[Last Name]

    Andres

    Friday, May 11, 2012 5:24 PM
  • Andre, thank you!  This was a really useful thread - I was having the same error in Access 2010 after having added an additional field to an existing query where I wanted to use filters.  The original query worked just fine but adding the field (which I've done in older versions successfully) immediately caused a syntax error across all of the fields.  I had been reluctant to save any of my several attempts because it wasn't working, but after reading this I took the plunge, saved and reopened the query, and it is all working perfectly now. 

    Mel

    Thursday, July 25, 2013 9:25 AM
  • Saving the query worked for me initially, but then stopped working.  Not sure why.
    Thursday, January 08, 2015 9:47 PM