Access Form - Syntax error (missing operator) in query expression

Question

I am receiving a syntax error in a form that I have created over a query. I created the form to restrict access to changing records. While trying to set filters on the form, I receive syntax errors for all attributes I try to filter on. I believe this has something to do with the lack of () around the inner join within the query code, but what is odd to me is that I can filter the query with no problem. Below is the query code:

SELECT CUSTOMER.[Product Number], SALESPERSON.[Salesperson Number],
SALESPERSON.[Salesperson Name], SALESPERSON.[Email Address]
FROM SALESPERSON INNER JOIN CUSTOMER ON
SALESPERSON.[Salesperson Number] = CUSTOMER.[Salesperson Number];

Any ideas why only the form would generate the syntax error, or how to fix this?

Mammilla

Since the query includes only one join, () should not be required. My best guess is the query is not the cause of the problem. It must be something due to the method you're using to set the filter or a problem with the filter expression string. – Berliner

You can eliminate the query by testing the SQL in the query designer. – Airwaves

The query you posted seems to work just fine. I think what you need to share with us is exactly how you are trying to filter the records. Please post the exact syntax and sample. – Zilpah

Query is working just fine, no filter issues at all. As far as how I am trying to filter, in the form I am clicking on the drop-down arrow for each attribute, this is when I get the error. I also noticed no data is listed for the records in the drop-down once it opens. I have found that I can filter by right-clicking on a filled cell though. – Mammilla

Bad convention to have spaces in database table field names. You can put spaces in aliases, but don't put spaces or special characters in the table field names (as several have stated). Your problem starts there. Control source syntax becomes a non-issue once you fix your underlying table field names. Doesn't appear to be a problem for you, but others with this error seem to also occasionally have spaces in table names. Also a bad idea. – Donela

Answer 1

I was able to quickly fix it by going into Design View of the Form and putting [] around any field names that had spaces. I am now able to use the built in filters without the annoying popup about syntax problems.

Beneficent

This fixed my problem. Thank you for the tip. – Corrinnecorrival

Answer 2

I had this same problem. As Dedren says, the problem is not the query, but the form object's control source. Put [] around each objects Control Source. eg: Contol Source: [Product number], Control Source: Salesperson.[Salesperson number], etc.

Makita recomends going to the original table that you are referencing in your query and rename the field so that there are no spaces eg: SalesPersonNumber, ProductNumber, etc. This will solve many future problems as well. Best of Luck!

Gablet

I don't know why people are downvoting you this actually solved my problem thank you. – Gettings

Also, take out the SalesPerson part of it and change Number to ID. Salesperson number should be changed to simply ID – Habiliment

Salesperson phone number => PhoneNumber or Phone – Habiliment

Answer 3

Try making the field names legal by removing spaces. It's a long shot but it has actually helped me before.

Jubilee

Tried within the query but no luck. – Mammilla

This worked for me, I replaced spaces with underscores – Hid

Yes. This. Don't replace with _, though. Capitalize each word. phone number becomes PhoneNumber or simply Phone – Habiliment

Answer 4

No, no, no.

These answers are all wrong. There is a fundamental absence of knowledge in your brain that I'm going to remedy right now.

Your major issue here is your naming scheme. It's verbose, contains undesirable characters, and is horribly inconsistent.

First: A table that is called Salesperson does not need to have each field in the table called Salesperson.Salesperson number, Salesperson.Salesperson email. You're already in the table Salesperson. Everything in this table relates to Salesperson. You don't have to keep saying it.

Instead use ID, Email. Don't use Number because that's probably a reserved word. Do you really endeavour to type [] around every field name for the lifespan of your database?

Primary keys on a table called Student can either be ID or StudentID but be consistent. Foreign keys should only be named by the table it points to followed by ID. For example: Student.ID and Appointment.StudentID. ID is always capitalized. I don't care if your IDE tells you not to because everywhere but your IDE will be ID. Even Access likes ID.

Second: Name all your fields without spaces or special characters and keep them as short as possible and if they conflict with a reserved word, find another word.

Instead of: phone number use PhoneNumber or even better, simply, Phone. If you choose what time user made the withdrawal, you're going to have to type that in every single time.

Third: And this one is the most important one: Always be consistent in whatever naming scheme you choose. You should be able to say, "I need the postal code from that table; its name is going to be PostalCode." You should know that without even having to look it up because you were consistent in your naming convention.

Recap: Terse, not verbose. Keep names short with no spaces, don't repeat the table name, don't use reserved words, and capitalize each word. Above all, be consistent.

I hope you take my advice. This is the right way to do it. My answer is the right one. You should be extremely pedantic with your naming scheme to the point of absolute obsession for the rest of your lives on this planet.

NOTE:You actually have to change the field name in the design view of the table and in the query.

Habiliment

Or in summary: Guidelines for naming – Cybil

Answer 5

Put [] around any field names that had spaces (as Dreden says) and save your query, close it and reopen it.

Using Access 2016, I still had the error message on new queries after I added [] around any field names... until the Query was saved.

Once the Query is saved (and visible in the Objects' List), closed and reopened, the error message disappears. This seems to be a bug from Access.

Agra

No. Don't put [] around it. You'll be doing that everywhere. Take out spaces, special characters, table names(such as Salesperson.Salesperson number), don't use reserved keywords, and you won't have to put [] around everything. – Habiliment

i tried removing spaces... but i still see the same issue being poping up.. not sure if am doing something wrong in the code? always getting suck at this line syntax error (Missing operator) in query expression 'AutoID=' – Jughead

I think it's an Access bug that also exists in version 2013 (so unlikely to be fixed). Several times now I've been in the Query Designer and when I view the query without saving it, this error appears if I attempt to filter on a column. After I save it and repeat the same steps, no error. Might have to do with spaces in the column/field names but haven't determined that yet. – Cybil

Answer 6

I did quickly fix it by going into "Design View" of the main Table of same Form and putting underline (_) between any field names that had spaces. I am now able to use the built in filters without the annoying popup about syntax problems.

Saire

Just take out the _ and capitalize each word. Make names short, don't repeat the table name. The shorter, the better. We don't want to hurt your poor fingers typing things over and over again. – Habiliment

Answer 7

Extra ( ) brackets may create problems in else if flow. This also creates Syntax error (missing operator) in query expression.

Fielder

Answer 8

I had this on a form where the Recordsource is dynamic.

The Sql was fine, answer is to trap the error!

Private Sub Form_Error(DataErr As Integer, Response As Integer)
'    Debug.Print DataErr

    If DataErr = 3075 Then
        Response = acDataErrContinue
    End If

End Sub

Elisha