Are any of these field Memo datatypes? I've run into strange things with
corrupt memo fields before. If any are Memos, try removing those fields from
the SQL and see if the union query then works.

Actually that might be a good troubleshooting method for the rest of the
fields. Remove some of them and see what does or doesn't work.

Another thought would be to create a union query with just two of the
tables, which you say works, then create another union query based on this
plus the third table. You might try different combinations of this to see if
there is a problem with a particular table.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Bruce" wrote:

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