Invalid bracketing problem

[NeilT123]

Yesterday I imported all of my data into a new database and now the following union query won't run. I get a message saying invalid bracketing of name Select[Field code

I understand from elsewhere on this forum that Access sometimes "rebrackets" queries but can anyone help with the correction.

Thank you in advance for any replies

SELECT [FieldCode], Sum(Effect) AS SumOfEffect, Yr
FROM [SELECT [FieldCode],NYr0 as Effect,CInt([OM Application Year]) As Yr FROM qry2rptA1
UNION ALL SELECT [FieldCode],NYr1,CInt([OM Application Year]+1) FROM qry2rptA1
UNION ALL SELECT [FieldCode],Nyr2,CInt([OM Application Year]+2) FROM qry2rptA1
]. AS U
GROUP BY [FieldCode], Yr;

 

[NeilT123]

I have managed to fix this. In case it is of any use to anyone else here is what I did.
Changed the [ before the SELECT in line 2 to (
Changed the ] in line 5 to ) and deleted the dot

This sorted out the problem but I notice that having run the query Access has changed the code to

SELECT [FieldCode], Sum(Effect) AS SumOfEffect, Yr
FROM [SELECT [FieldCode],NYr0 as Effect,CInt([OM Application Year]) As Yr FROM qry2rptA1
UNION ALL SELECT [FieldCode],NYr1,CInt([OM Application Year]+1) FROM qry2rptA1
UNION ALL SELECT [FieldCode],Nyr2,CInt([OM Application Year]+2) FROM qry2rptA1
]. AS U
GROUP BY [FieldCode], Yr;

which is exactly as above!!!!!

 

[JerryKlmns]

NeilT123

That makes us two! I face the same in a similar union query.
I hope some1 can explain why...

 

[PHV]

This is a known bug.
A work around is to create the union query named, say, qryU2rptA1:
SELECT [FieldCode],NYr0 as Effect,CInt([OM Application Year]) As Yr FROM qry2rptA1
UNION ALL SELECT [FieldCode],NYr1,CInt([OM Application Year]+1) FROM qry2rptA1
UNION ALL SELECT [FieldCode],Nyr2,CInt([OM Application Year]+2) FROM qry2rptA1

And now your query:
SELECT [FieldCode], Sum(Effect) AS SumOfEffect, Yr
FROM qryU2rptA1
GROUP BY [FieldCode], Yr

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JerryKlmns]

Thank you PHV for sharing this info.