If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|