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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Guys am facing similar issue here is my full code Do let me know where am i going wrong  Error message  syntax error  Missing operator  in query expression  AutoID   This only hapens when i click on login without entering any txt in either combobox and password field      Option Compare Database Option Explicit  Private Sub Login Click    If IsNull Me ComboUserSelect Value  Then     MsgBox  quot Please select username quot   vbInformation   quot Login ID Required quot      Me ComboUserSelect SetFocus ElseIf IsNull Me txtpassword Value  Then     MsgBox  quot please enter password quot   vbInformation   quot Password is Required quot      Me txtpassword SetFocus End If                 Declaring the variables              Dim passwordindatabase As String Dim typedpassword As String Dim useraccesstype As String   passwordindatabase   DLookup  quot Password quot    quot LoginDB quot    quot AutoID  quot   amp  ComboUserSelect Value  typedpassword   txtpassword Value useraccesstype   DLookup  quot AccessType quot    quot LoginDB quot    quot AutoID  quot   amp  ComboUserSelect Value    If typedpassword   passwordindatabase Then     If useraccesstype    quot Admin quot  Then     DoCmd OpenForm   quot Cam Infra quot       DoCmd Close acForm   quot Login Form quot   acSaveNo     Else     If useraccesstype    quot user quot  Then     DoCmd OpenForm   quot Custom Search Form quot       DoCmd Close acForm   quot Login Form quot   acSaveNo     End If     End If     End If       End Sub

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to forms

Examples related to ms-access

Examples related to syntax-error