syntax error (missing operator)

  • Thread starter Thread starter Pamela Creighton
  • Start date Start date
P

Pamela Creighton

I have something strange that just started happening to a dbase I create a
query in design view and run it and it is fine, then the next day I run it
or later that day and it comes back with
syntax error (missing operator) in query expression and then it turns it
into sql query vs. design view and I have to recreate the query. I have
tried bringing into a new dbase and it continues, It happens every time I
create a query from within a report as well.

Any idea would be appreciated.
 
Post the SQL statement of the query.

Also, be sure that Name AutoCorrect is turned off in your database, as it's
known to cause these types of problems.
 
I did just turn off the Name AutoCorrect and will try again and see what
happens

This is what it changes to from the design view
SELECT [tblMasterList-ApplicantCompany].ApplicantOrgID,
[tblMasterList-ApplicantCompany].OrganizationName,
[tblMasterList-ApplicantCompany].UNION ,
[tblMasterList-ApplicantCompany].Inactive FROM
[tblMasterList-ApplicantCompany] WHERE
((([tblMasterList-ApplicantCompany].Inactive)=0))
ORDER BY [tblMasterList-ApplicantCompany].OrganizationName;
 
The word UNION is a reserved word in SQL. I suspect that may be the cause
of the problem.

You might try wrapping it in brackets [] and see if that cures the problem.
If it does, you probably should rename field to something else such as
theUnion or UnionName.

SELECT [tblMasterList-ApplicantCompany].ApplicantOrgID,
[tblMasterList-ApplicantCompany].OrganizationName,
[tblMasterList-ApplicantCompany].[UNION],
[tblMasterList-ApplicantCompany].Inactive
FROM [tblMasterList-ApplicantCompany]
WHERE [tblMasterList-ApplicantCompany].Inactive=0
ORDER BY [tblMasterList-ApplicantCompany].OrganizationName

Pamela Creighton said:
I did just turn off the Name AutoCorrect and will try again and see what
happens

This is what it changes to from the design view
SELECT [tblMasterList-ApplicantCompany].ApplicantOrgID,
[tblMasterList-ApplicantCompany].OrganizationName,
[tblMasterList-ApplicantCompany].UNION ,
[tblMasterList-ApplicantCompany].Inactive FROM
[tblMasterList-ApplicantCompany] WHERE
((([tblMasterList-ApplicantCompany].Inactive)=0))
ORDER BY [tblMasterList-ApplicantCompany].OrganizationName;

Ken Snell (MVP) said:
Post the SQL statement of the query.

Also, be sure that Name AutoCorrect is turned off in your database, as
it's known to cause these types of problems.
Click to expand...
Click to expand...
 
I will rename the field and modify queries, forms, reports - I never even
thought of that. Always the obvious when you share with some one.
Thanks you

John Spencer said:
The word UNION is a reserved word in SQL. I suspect that may be the cause
of the problem.

You might try wrapping it in brackets [] and see if that cures the
problem. If it does, you probably should rename field to something else
such as theUnion or UnionName.

SELECT [tblMasterList-ApplicantCompany].ApplicantOrgID,
[tblMasterList-ApplicantCompany].OrganizationName,
[tblMasterList-ApplicantCompany].[UNION],
[tblMasterList-ApplicantCompany].Inactive
FROM [tblMasterList-ApplicantCompany]
WHERE [tblMasterList-ApplicantCompany].Inactive=0
ORDER BY [tblMasterList-ApplicantCompany].OrganizationName

Pamela Creighton said:
I did just turn off the Name AutoCorrect and will try again and see what
happens

This is what it changes to from the design view
SELECT [tblMasterList-ApplicantCompany].ApplicantOrgID,
[tblMasterList-ApplicantCompany].OrganizationName,
[tblMasterList-ApplicantCompany].UNION ,
[tblMasterList-ApplicantCompany].Inactive FROM
[tblMasterList-ApplicantCompany] WHERE
((([tblMasterList-ApplicantCompany].Inactive)=0))
ORDER BY [tblMasterList-ApplicantCompany].OrganizationName;

Ken Snell (MVP) said:
Post the SQL statement of the query.

Also, be sure that Name AutoCorrect is turned off in your database, as
it's known to cause these types of problems.

--

Ken Snell
<MS ACCESS MVP>

I have something strange that just started happening to a dbase I create
a query in design view and run it and it is fine, then the next day I
run it or later that day and it comes back with
syntax error (missing operator) in query expression and then it turns
it into sql query vs. design view and I have to recreate the query. I
have tried bringing into a new dbase and it continues, It happens every
time I create a query from within a report as well.

Any idea would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top