Union Query Syntax Error (1 Viewer)

R

Rik_StHelens

Registered User.
Local time
Today, 16:55
Joined
Sep 15, 2009
Messages
164
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

Code: 
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
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 17:55
Joined
Aug 11, 2003
Messages
11,695
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 :D, which I hope with this information you can now fix yourself :)
 
R

Rik_StHelens

Registered User.
Local time
Today, 16:55
Joined
Sep 15, 2009
Messages
164
Great.
got that one sorted thanks very much.

having another problem now with the parameter clause in the following code
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
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 17:55
Joined
Aug 11, 2003
Messages
11,695
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
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom