Union Query Syntax Error

[Rik_StHelens]

I have the following sql code which i have written to search our current and archived records by vehicle registration number. I get an error in the FROM clause, but as i don't really know sql (i poached the code from forums) im not sure why

SELECT CUSTOMER, INVNUM, [CAN Number], DELIVERY, INVDATE, [Status Lookup], Destination, REGNUM, POSITION, TREADM, SERIALON, SERIALOFF, SECTION, PROFILE, RIM, SIZE, [New or Rems], STCODE, Brand, [Removal Description], DESCRIPN, [Post Code], OLDADVNUM, [Retreader Agent], SUMCODE, NAME1,VAN
FROM ALLCasingJobs
Union All
SELECT CUSTOMER, INVNUM, [CAN Number], DELIVERY, INVDATE, [Status Lookup], Destination, REGNUM, POSITION, TREADM, SERIALON, SERIALOFF, SECTION, PROFILE, RIM, SIZE, [New or Rems], STCODE, Brand, [Removal Description], DESCRIPN, [Post Code], OLDADVNUM, [Retreader Agent], SUMCODE, NAME1,VAN
FROM Historical Casing Jobs
Union All
WHERE ((([AllCasingJobs].[REGNUM]) or ([Historical Casing Jobs].[REGNUM]) Like [Vehicle Reg]));

Where have i gone wrong?

Thanks for your help

 

[namliam]

Each query has to be a valid query, and is seperated by the "union all"

Valid query
Union
Valid query
Union
valid Query

Your syntax is invalid , which I hope with this information you can now fix yourself

 

[Rik_StHelens]

Great.
got that one sorted thanks very much.

having another problem now with the parameter clause in the following code

PARAMETERS [CAN Number] Text ( 255 );
SELECT [Historical Casing Jobs].CUSTOMER, [Historical Casing Jobs].INVNUM, [Historical Casing Jobs].[CAN Number], [Historical Casing Jobs].DELIVERY, [Historical Casing Jobs].INVDATE, [Historical Casing Jobs].Destination, [Historical Casing Jobs].REGNUM, [Historical Casing Jobs].POSITION, [Historical Casing Jobs].TREADM, [Historical Casing Jobs].SERIALON, [Historical Casing Jobs].SERIALOFF, [Historical Casing Jobs].SECTION, [Historical Casing Jobs].PROFILE, [Historical Casing Jobs].RIM, [Historical Casing Jobs].SIZE, [Historical Casing Jobs].[New or Rems], [Historical Casing Jobs].STCODE, [Historical Casing Jobs].Brand, [Historical Casing Jobs].[Removal Description], [Historical Casing Jobs].DESCRIPN, [Historical Casing Jobs].[Post Code], [Historical Casing Jobs].OLDADVNUM, [Historical Casing Jobs].[Retreader Agent], [Historical Casing Jobs].SUMCODE, [Historical Casing Jobs].NAME1, [Historical Casing Jobs].VAN, [Historical Casing Jobs].[Status Lookup]
FROM [Historical Casing Jobs]
UNION ALL
PARAMETERS [CAN Number] Text ( 255 )
SELECT AllCasingJobs.CUSTOMER, AllCasingJobs.INVNUM, AllCasingJobs.[CAN Number], AllCasingJobs.DELIVERY, AllCasingJobs.INVDATE, AllCasingJobs.Destination, AllCasingJobs.REGNUM, AllCasingJobs.POSITION, AllCasingJobs.TREADM, AllCasingJobs.SERIALON, AllCasingJobs.SERIALOFF, AllCasingJobs.SECTION, AllCasingJobs.PROFILE, AllCasingJobs.RIM, AllCasingJobs.SIZE, AllCasingJobs.[New or Rems], AllCasingJobs.STCODE, AllCasingJobs.Brand, AllCasingJobs.[Removal Description], AllCasingJobs.DESCRIPN, AllCasingJobs.[Post Code], AllCasingJobs.OLDADVNUM, AllCasingJobs.[Retreader Agent], AllCasingJobs.SUMCODE, AllCasingJobs.NAME1, AllCasingJobs.VAN, AllCasingJobs.Status
FROM AllCasingJobs

again i dont really know sql (but i am learning.....slowly...) so any help on why this occurs would be great.

Thanks again

 

[namliam]

I ask the question and repeat the suggestion:
Each query has to be a valid query, and is seperated by the "union all"

Valid query
Union
Valid query
Union
valid Query

Beyond that
1) SQL
When posting sql, make sure it is READABLE and endless line of select.... IS NOT
Select Field
, field
from table
join table
union
...

is... MUCH MORE readable, thanks

2)
Beyond valid sql both / all queries in a union must have the same number of columns and must have the same column types accross the Union's


Your syntax is invalid , which I hope with this information you can now fix yourself