Syntax error (missing operator) in query expression <field name>

[RosscoPeco]

Hi there
I am using Access 2010 and displaying the contents of a query in a form. When I click on one of the column headers (ie where you sort and filter) I get the above error. I can click Ok and then am able to sort the column but it doesn't show any of the values to filter on. Not sure where to start fixing as it is an in built function of access.

It is also strange that I have code in my form that applies a filter to the office and department fields, but when the form loads no filter is applied and I don't get an error on these fields. Hope this makes sense.


The query that the form is based on is below if that helps:

SELECT [FY2012 Source].[Staff Code], [FY2012 Source].[Surname], [FY2012 Source].[Preferred Name], [FY2012 Source].[Date Joined], [FY2012 Source].[Department], [FY2012 Source].[Office], [FY2012 Source].[Draft Grade], [FY2012 Source].[Proposed Grade], [FY2012 Source].[Participating in review process] FROM [FY2012 Source];

Any help would be appreciated.

 

[speakers_86]

I've never seen this before, but it may be corruption. Did you compact and repair? How about making a new shell? Has this issue appeared before? Is the database in development, or actually in use?

As for your filter, check out a form property called something like Apply sort on load. Setting the value to true should help. Or you can ditch that code, and apply the sort in the query.

 

[RosscoPeco]

Hi speaker_86

Thanks for your response.

I tried everything you mentioned but to no avail eg I tried exporting the table from the query to a new access database but it seemed to filter ok.

The database is in development. I have just disabled filtering on the form till I figure it out.

The primary issue is that the error message is just not helpful and I don't know where to start in trying to debug the error, I don't know how to see what the sql (or filter string) looks like that is providing the error???

Thanks again.