Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 498
FGLC0983 Avatar asked Feb 07 '14 20:02

FGLC0983


People also ask

How do you add an expression to a query in Access?

Select the control into which you want to enter an expression. If the Property Sheet is not already displayed, press F4 to display it. To manually create an expression, on the Data tab in the property sheet, click the ControlSource property of the text box, and then type = followed by the rest of your expression.

What does missing operator mean SQL?

Usually it is a misspelled table or field name, two spaces instead of just one, a space instead of "_", any missing letter or something like that. Create a new query in MS Access and put the whole query in, then run it.

What does #error mean in Access?

#Error. The #Error error value means that Access cannot evaluate an expression. For example, you may have supplied incorrect or too few arguments for an aggregate (totals) function, you may have used a parameter query as the domain for an aggregate function, or you may have made a circular reference in the expression.


2 Answers

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.

like image 199
Dedren Avatar answered Oct 05 '22 12:10

Dedren


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!

like image 37
msllarson2 Avatar answered Oct 05 '22 13:10

msllarson2