New Table Created - Query Syntax Error (Missing Operator) in Query Express (1 Viewer)

RonaldRRolf · Dec 20, 2019

I had a database where the primary table (call it table1) became corrupt. I could not open it, yet the queries I created that linked to the table1 still worked. At this time I was adding more fields to the table and subsequently to the From that fed the table. After I added those then the table was corrupted somehow. My IT could not recover it so I just used one of those queries to export all the fields to an Excel worksheet. Then I imported that worksheet to Access and named that "Table2". I went to all my queries and put the source as "Table2" . All appeared to be working fine, but when I go to a query that has some criteria defined, (say I wanted all records in month of January) or try to add some a new criteria, the query will run but if I try to sort on any field I get a "syntax error (missing operator) in query expression." Even when I clear all criteria from the query I will still get that error.

Can anyone help me fix this?

Thanks

Ron

Pat Hartman · Dec 20, 2019

Start by turning off the NameAutoCorrect "feature". Go to File/Access Options to find it. This "feature" can bite you when you change object names and don't understand how Access is "helping" you behind the scenes.

Sometimes errors are caused because you selected SAVE when closing a table or query so there may be residual sorts on your tables or queries that you need to remove.

In the future, once you have NameAutoCorrect permanently turned off, if this ever happens again, rename the bad table so that you can reuse the original table name for the rebuilt version and not have to change anything else. However, leaving NameAutoCorrect on will wreck your chances of doing this successfully since Access will attempt to change your queries, forms, and reports to use the "new" name when you rename an object.

Name Auto Correct has its uses but YOU need to turn the feature on when you want Access to help you so you can make sure that all the changes that need to happen, happen. The reason that NameAutoCorrect causes problems is because it doesn't change everything immediately. Objects that are not opened don't get changed until you open them and you might have gone through three iterations of change between th e original and the final versions and so NameAutoCorrect may make changes you don't want.

theDBguy · Dec 20, 2019

Hi Ron. At times when I get that error when filtering/sorting a datasheet, it usually means there are null values in the data. Can you check?

RonaldRRolf · Dec 20, 2019

Pat Hartman said:
Start by turning off the NameAutoCorrect "feature". Go to File/Access Options to find it. This "feature" can bite you when you change object names and don't understand how Access is "helping" you behind the scenes.

Sometimes errors are caused because you selected SAVE when closing a table or query so there may be residual sorts on your tables or queries that you need to remove.

In the future, once you have NameAutoCorrect permanently turned off, if this ever happens again, rename the bad table so that you can reuse the original table name for the rebuilt version and not have to change anything else. However, leaving NameAutoCorrect on will wreck your chances of doing this successfully since Access will attempt to change your queries, forms, and reports to use the "new" name when you rename an object.

Name Auto Correct has its uses but YOU need to turn the feature on when you want Access to help you so you can make sure that all the changes that need to happen, happen. The reason that NameAutoCorrect causes problems is because it doesn't change everything immediately. Objects that are not opened don't get changed until you open them and you might have gone through three iterations of change between th e original and the final versions and so NameAutoCorrect may make changes you don't want.

Pat, I did that but still have the problem. One query has the criteria as being ">12/31/18" under a Date field. That was how the original query was before the table1 went corrupt. If I change that to ">12/30/18" then I get this error when trying to sort the data. The query actually runs just fine. And the error comes up when I try to sort ANY field in the query, not just under the "date field" . I thought the issue may just be to those existing queries so I created a new one from scratch but which pulled info from the rebuilt table (table2). The error is still there. Then I created a query off another table in this database and this was not renamed or corrupted. Same error. That tells me the issue is not in the rebuilt table. Any other ideals?

RonaldRRolf · Dec 20, 2019

RonaldRRolf said:
Pat, I did that but still have the problem. One query has the criteria as being ">12/31/18" under a Date field. That was how the original query was before the table1 went corrupt. If I change that to ">12/30/18" then I get this error when trying to sort the data. The query actually runs just fine. And the error comes up when I try to sort ANY field in the query, not just under the "date field" . I thought the issue may just be to those existing queries so I created a new one from scratch but which pulled info from the rebuilt table (table2). The error is still there. Then I created a query off another table in this database and this was not renamed or corrupted. Same error. That tells me the issue is not in the rebuilt table. Any other ideals?

I also seen a Name AutoCorrect Save Failures. But it shows the old corrupted table as not being able to be saved. The feature under OPTIONS does show this has been disabled though.

isladogs · Dec 20, 2019

RonaldRRolf said:
One query has the criteria as being ">12/31/18" under a Date field. That was how the original query was before the table1 went corrupt. If I change that to ">12/30/18" then I get this error when trying to sort the data. The query actually runs just fine.

If you use quotes on a date field you will get a datatype mismatch error.
You need to use date delimiters for date fields and I strongly recommend using 4 digits for years e.g. >#12/31/2018#

RonaldRRolf · Dec 20, 2019

isladogs said:
If you use quotes on a date field you will get a datatype mismatch error.
You need to use date delimiters for date fields and I strongly recommend using 4 digits for years e.g. >#12/31/2018#

Not using Quotes. Just put that in the message to emphasize the value. The query works with the values I added, it is just that when you look at the results and try to sort the field, then that syntax message comes up. Once you click OK on the error message box then the sort /filter option box does come up and you can then sort or filter. Just don't know why this error box is coming up.

Pat Hartman · Dec 20, 2019

In your example you said:

> 12/31/2018

Make sure it is actually

>#12/31/2018#

theDBguy · Dec 20, 2019

RonaldRRolf said:
Not using Quotes. Just put that in the message to emphasize the value. The query works with the values I added, it is just that when you look at the results and try to sort the field, then that syntax message comes up. Once you click OK on the error message box then the sort /filter option box does come up and you can then sort or filter. Just don't know why this error box is coming up.

Hi Ron. Just to make sure I am not being ignored, can you please acknowledge if you saw my post earlier? Thanks!

Just wanted to know if it wasn't any help at all. Cheers!

RonaldRRolf · Dec 20, 2019

theDBguy said:
Hi Ron. At times when I get that error when filtering/sorting a datasheet, it usually means there are null values in the data. Can you check?

No Null values. Assume you have read the other updates since this to see how its progressing.

theDBguy · Dec 20, 2019

RonaldRRolf said:
No Null values. Assume you have read the other updates since this to see how its progressing.

Hi Ron. Thanks for the update. Yes, I have read all the other posts, but there was no mention of having checked for null values, and I didn't want to assume anything. Cheers!

New Table Created - Query Syntax Error (Missing Operator) in Query Express (1 Viewer)

RonaldRRolf

New member

Pat Hartman

Super Moderator

theDBguy

I’m here to help

RonaldRRolf

New member

RonaldRRolf

New member

isladogs

MVP / VIP

RonaldRRolf

New member

Pat Hartman

Super Moderator

theDBguy

I’m here to help

RonaldRRolf

New member

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread