syntax error (missing operator) in query expression in datasheet view of form

[fredj5000]

I have been using the same data file for 3 years now for my personal sales CRM, and have had issues with my everyday contact form. In the datasheet view om my form, when clicking on the drop down to filter records I get the "syntax error (missing operator) in the query expression ..." on 3 fields (callback date, contact name, & company name). It did not used to to this, but has been for about a year now. This was all created with GUI, and I haven't written code in 20 years. Where do I even look?

[Bulzie]

Can you give more detail. So you have a form you open in Datasheet view and if you click on any of those 3 fields to filter by drop down, once you select something it gives you that error? Do you have other fields in that datasheet form that you can filter that does not give you that error? Is there data in those fields for the value you are filtering?

[fredj5000]

That was quick. Thank you!

When I click on the drop down I get the error message, I click OK, and then I can filter. There are 20 fields in the datasheet, and this error only occurs on those 3 fields. Most of the records have a value, but some are blank. That was never an issue before the error message appeared. I used to be able to check/uncheck dates from the drop down, and it just disappeared a while back.

[Bulzie]

So the error appears when you just click on the arrow to bring up the options I believe you said, but when you click ok, then you can go select what you want and it does the filter?

[fredj5000]

That is correct. What is missing is a list of options to check/uncheck. I have to manually enter values to search for. It is like it lost its index.

[Gicu]

Hi, this is a known issue with datasheet sorting/filtering, and it happens because of your space in the field names, enclose them in square brackets and it should be fine:
https://social.msdn.microsoft.com/Fo...orum=accessdev

Cheers,

[Bulzie]

I have not had this issue, I do not use datasheet view often. Just getting more info incase someone else has seen this. Is the Callback date a string or an actual date datatype?

Maybe try a few tests. Create a new datasheet form and only add those 3 fields and see if it does the same thing. If it does not then it is something with your form. Maybe create a new form and copy/paste the objects from old to new form and see if still get issue?

[fredj5000]

Do I change the field name in the source table or in the form somewhere?

[Bulzie]

I would not change anything in the table yet, try to create a new blank form, add that table as the Recordsource and just add one of those fields to the form(make it a datasheet form), open it and see if you can do the filter and not get the error? Trying to see if it is you form or the data/table that has the issue.

[fredj5000]

Putting bracket around the field names fixed it. Thank you all!

[fredj5000]

Well... almost. Now I get data type mismatch when selecting 'Today' from the date drop down. I feel like a rookie.

[Bulzie]

If date maybe try # around the value.

[Gicu]

What is the recordsource of the form? If it is a Select statement string try save it as a query and use that instead, these problems usually go away if you use saved queries as your recordsource.

Cheers,