View Single Post
  #1  
Old May 20th, 2010, 12:56 PM posted to microsoft.public.access.queries
Bruce
external usenet poster
 
Posts: 374
Default Union Query Access 2007 SP2 MSO

I have a union query that keeps telling me that there is a data type
mismatch. When I first run the query everything is okay and it displays the
fields accordingly. Then using the record selector I tell it to go to the
last record. The query runs a little bit and then sends the error message
Data type mismatch in criteria expression. (Error 3464).

I have triple checked all the fields to make sure they are in the correct
order and have the correct data. I can run the query with any combination of
two queries and there is no problem. A check of the records reveals that each
query is compatible with the other. I have a Parts query with 44,905 records,
a Labor query with 49,956 records and a Sublets query with 11,239 records.
The only time I get the error is when I have three queries within the Union.
Parts and Sublet produce 561,144 records, Parts and Labor produce 94,861
records; Labor and Sublet produce 61,195 records all with no errors. My code
is listed below:

SELECT [WO Master Parts 03].BD, [WO Master Parts 03].Department, 1 AS
Segment, [WO Master Parts 03].WorkOrderUID, [WO Master Parts
03].WorkOrderNumber, [WO Master Parts 03].WOServiceUID, [WO Master Parts
03].Type, [WO Master Parts 03].NPD, [WO Master Parts 03].Total, [WO Master
Parts 03].Quantity, [WO Master Parts 03].StoreRoom, [WO Master Parts
03].PartNumber, Null AS StartDate, Null AS EndDate, Null AS HrTime
FROM [WO Master Parts 03]

UNION ALL SELECT [WO Master Labor 03].BD, [WO Master Labor 03].Department, 2
AS Segment, [WO Master Labor 03].WorkOrderUID, [WO Master Labor
03].WorkOrderNumber, [WO Master Labor 03].WOServiceUID, [WO Master Labor
03].Type, [WO Master Labor 03].NPD, [WO Master Labor 03].Total, Null AS
Quantity, Null AS StoreRoom, Null AS PartNumber, [WO Master Labor
03].StartDate, [WO Master Labor 03].EndDate, [WO Master Labor 03].HrTime
FROM [WO Master Labor 03]

UNION ALL SELECT [WO Master Sublet 03].BD, [WO Master Sublet 03].Department,
3 AS Segment, [WO Master Sublet 03].WorkOrderUID, [WO Master Sublet
03].WorkOrderNumber, [WO Master Sublet 03].WOServiceUID, [WO Master Sublet
03].Type, [WO Master Sublet 03].NPD, [WO Master Sublet 03].Total, Null AS
Quantity, Null AS StoreRoom, Null AS PartNumber, Null AS StartDate, Null AS
EndDate, Null AS HrTime
FROM [WO Master Sublet 03];

Can anyone assist me and tell me the error of my code?? Please

--
Bruce
Ads