Access Form - Syntax error (missing operator) in q

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?

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

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.