Invalid syntax error on exisitng queries - unable to edit

[Roonmeister99]

Hi,



We are suddenly getting "the expression you entered contains invalid syntax" error on some existing queries which have not been amended. Once we click ok on the error message, the query shuts down so we cannot view or edit.

Any ideas as to why this is happening?

Many thanks

R

[Micron]

This sort of thing is usually caused by program updates that introduce bugs, or the updates cause malformed code/expressions to no longer work. If it's happening on all pc's check the build and versions of Access on them and if recent, try rolling the update back on one and see what happens. You could also open the query in design view, copy and post the sql and maybe someone will spot something that's not quite right.

[Roonmeister99]

This sort of thing is usually caused by program updates that introduce bugs, or the updates cause malformed code/expressions to no longer work. If it's happening on all pc's check the build and versions of Access on them and if recent, try rolling the update back on one and see what happens. You could also open the query in design view, copy and post the sql and maybe someone will spot something that's not quite right.

Many thanks for your reply. I get the same when I try and open in design view - error message displayed and then the query shuts immediately
I will try rolling back updates.

[ArviLaanemets]

Did you open it in SQL View? Or only in default design view?

[Roonmeister99]

Did you open it in SQL View? Or only in default design view?

I can't see SQL view, can only open in design view.

[CarlettoFed]

If you attach a sample file we could check it directly and possibly advise you.

[Micron]

You cannot right click on the query name in the nav pane and get sql view option?

[ArviLaanemets]

You cannot right click on the query name in the nav pane and get sql view option?

Haven't tried this, but maybe someone knows a script to read the querystring to variable, and display it (the querystring is a property of query, so it must be possible to read it). OP runs the the script and saves the querystring. Then finds out what is wrong, and makes corrections to querystring. Then renames old query, creates a new one, pasting corrected querystring into SQL view, and saves the new query with old one' s name. As last step, tests the app, and when all is OK, then deletes renamed old query.

I myself can't help with this, as I am on sick leave, and I don't have Office installed at my home computer.

[CJ_London]

a script to read the querystring to variable

to assign a query's sql to a variable is simply

dim sqlstr as string
sqlstr=currentdb.QueryDefs("name of query").SQL

or just type
?currentdb.QueryDefs("name of query").SQL
in the vba immediate window


Appreciate you are saying you cannot open in design view but if you can, to view the sql, click on the design ribbon


or the bottom right of the access window


I would also check your vba library for missing references - open the vba window, click on Tools, then References


The first 3 are required as a minimum - you may have a different version number for the Object Library - the version number depends on the version of Access you are using

Failing that, although very rare, it may be the query object has become corrupted - this can happen if multiple users are using the same front end. If that is the case, the solution is to revert to a backup of the front end. (I assume your db is split)

[Micron]

Sigh, another false memory. It's not an option if you right click on a nav pane query name.