Sub query replaces parenthesis with brackets

[zombie5268]

Hi,
I have a query I wrote that looks like the 1st SQL below. I run it and it works fine. I save it, close it and reopen it and it looks like the 2nd SQL below. At this point I have to change it back to the 1st SQL to run or I get an an error like the screen shot attached.

Any idea what's happening when I close and reopen it???

SELECT COUNT(EMP_ID)
FROM (SELECT DISTINCT EMP_ID FROM tbl4thQuarter_Trimmed WHERE (BF_Risk="Average"));

SELECT COUNT(EMP_ID)
FROM [SELECT DISTINCT EMP_ID FROM tbl4thQuarter_Trimmed WHERE (BF_Risk="Average"); ] AS [%$##@_Alias];

 

[boblarson]

Have you tried:

SELECT COUNT([COLOR="red"][[/COLOR]EMP_ID[COLOR="Red"]][/COLOR])
FROM (SELECT DISTINCT [COLOR="red"][[/COLOR]EMP_ID[COLOR="red"]][/COLOR] FROM tbl4thQuarter_Trimmed WHERE ([COLOR="red"][[/COLOR]BF_Risk[COLOR="red"]][/COLOR]=[COLOR="red"]'[/COLOR]Average[COLOR="red"]'[/COLOR]));

 

[zombie5268]

Hi Bob,
Thanks for the advice. It still replaces the code when I close and reopen to the SQL below, but at least it runs with this change and no error message.

You're a genius!!

SELECT COUNT([EMP_ID])
FROM [SELECT DISTINCT [EMP_ID] FROM tbl4thQuarter_Trimmed WHERE ([BF_Risk]='Average')]. AS [%$##@_Alias];

Thanks again,
Mitch

 

[boblarson]

By the way - I forgot to give you this:

 

[zombie5268]

HEY, HEY...Thanks!!

I've been Googling this all morning and the only resolution I could find was to break out the Sub Queries into seperate ones. Not exactly what I wanted to do.

You're a lifesaver!!

 

[datAdrenaline]

The bracket modification is how the Access "Opitmizer"/Compiler/What-cha-majig behaves...

You create this really nice looking SQL with a sub-query, then POW! ... Access with drop the parens and add square backets and a period, when the query optimization is saved. Note that the execution plan and "Optimized" sql are still functional with each execution of the query .. BUT .. If you change the design of the query object, you will find that square brackets inside of square brackets is NOT allowed ...

Your second SQL statement in your original post will NOT be valid simply because it is missing the period after the closing square bracket, which actually tells JET that all the stuff inbetween the square brackets is a table or query name .... so the DOT means a LOT! ...

SELECT COUNT(EMP_ID)
FROM [SELECT DISTINCT EMP_ID FROM tbl4thQuarter_Trimmed
WHERE (BF_Risk="Average")]. AS [%$##@_Alias];

You can read more about the phenomenon ...
Access Changes the SQL Statement #1
Access Changes the SQL Statement #2

Also, just as an FYI ...

- A97 will only accept the square bracket format
- A2000 -> A2003 "optimizes" the SQL statement ... how nice eh?
- A2007 allows, and keeps, either format

 

[Coderunner]

Hi everybody! I found a reason for loosing the "." in the SQL. When I turned off the "objectname autocorrect" (my access is in german, don't know the english translation) it still converts the subqueries, but the "." stays in place and the query works.