For awhile now, I've noticed that Access seems to alter the syntax of some of my more complicated queries when I save and reopen them. This happens consistently when I use subqueries. For instance:
FROM (SELECT A FROM TABLE) AS SUBQ;
is consistently changed to:
FROM [SELECT A FROM TABLE]. AS SUBQ;
I've been able to live with this since at least it happens consistently. But lately I've been having a couple of more difficult problems.
First, Access will report a (new) syntax error when I try to open a query. It will then immediately close the query window, not giving me a chance to debug. I have to use DAO to print out the existing query, change it, and revise the querydef.SQL property. Grrrr!!!
Perhaps even worse, I'm getting an error message when I simply try to change the *format* of a query (one that I've been able to resave, close, reopen, and run). It starts with:
"Syntax error (missing operator) in query expression..." [sql fragment follows]
As far as I can tell in this case it is the parentheses that are being messed with.
Has anybody else dealt with this? Can you point me to existing posts on this topic (I was unable to find any using search)? Am I missing anything obvious?
I have had the QBE re-parse out some of my more complicated queries, usually in the where clauses, and send me a completely different resultset than I wanted the query to do.
In all of those cases where I needed the query to be left unchanged I was forced to issue a statement that the jet could not resolve and hence let the query be as I designed it.
What I did in the where clause was enclose one of the where references in a trim([fieldName]) and the query worked as designed. Though when you do that you sometimes lose the QBE window and can only see the SQL after that.
Without your actual full SQL syntax you won't get a lot of help here.
Thanks for your replies. The example was not intended as real query, but was just to illustrate the problem -- that my parentheses are changed to brackets and a period is inserted after the closing bracket. This happens with all subqueries that I write. I'll see if I can find a suitable example.
I've had a similar problem with losing the QBE window on occasion. Oddly, if I copy the sql text to a new query and save it I can usually recover the QBE window that way.