Invalide Bracketing Query Error - How do I stop this from happening???

[KellyStee]

OK, this drives me nuts and I'm finally going to post this thread on here to see if someone can tell me what I'm doing wrong. I write queries like the one below a lot with an embedded Select. The query works fine; my problem is (and this only happens sometimes which is a source of frustration all in itself) when I close the query and re-open it, Access makes some formatting modificiations to the query and then tells me it's invalid!! UGH!
Original Code:
Select CV, [Wages]
From (Select left([Employee_ID], 17) as CV,
max([Date_Hired]) as DH
From Employees
Group By Left([Employee_ID], 17)) A,
Employees I
Where A.CV = Left([Employee_ID], 17)
and I.[Date_Hired] = A.DH

Modified Code (by Access):
Select CV, [Wages]
From [Select left([Employee_ID], 17) as CV,
max([Date_Hired]) as DH
From Employees
Group By Left([Employee_ID], 17)]. as A,
Employees I
Where A.CV = Left([Employee_ID], 17)
and I.[Date_Hired] = A.DH

Basically, Access puts brackets around my embedded select, so when I open the query, Access tells me I have "invalid bracketing" in a message box with an OK button and a Help button and there seems to be no way to get around this message box to fix the invalid bracketing. It just comes up with an error, I hit OK, and it shuts the query, over and over again! ARGH! So I end up re-typing the query each time I need to use it. Is there a better way to write my query so it doesn't put these brackets where my parantheses go??

Thanks!!
Kelly

 

[r937]

it might be an idea to store your subquery as a query

save this query --

Select left(Employee_ID, 17) as CV
, max(Date_Hired) as DH
From Employees
Group By Left(Employee_ID, 17)

open it and run it and save it and verify that it remains unaltered

then just re-write your original query as

Select CV
, Wages
From savedquery A
, Employees I
Where A.CV = Left(Employee_ID, 17)
and I.Date_Hired = A.DH

rudy