Datatype mismatch in Union query (1 Viewer)

Darrell

Registered User.
Local time
Today, 04:47
Joined
Feb 1, 2001
Messages
299
Just for a change I have something that was working perfectly for well over a year that has now stopped working and is causing vast amounts of :banghead::banghead::banghead:

I am doing analysis of production operation times and as such have two tables - one which has production order header info and the other which has production order operation detail.

In this operation table I have two fields that record whether or not an operation for a production order has been checked and one to say whether or not it should be ignored in analysis etc.

So based on these tables I have two select queries - one which reports all production orders which have been checked and one which reports those that haven't been checked.

Using these, I have a Union query that will then give me a report by Material number showing production order operations that were checked and the new production order operations that haven't been checked.

The main reason for doing it all this way is that the operations table doesn't have the material number in it.

So this all has been working perfectly up until today where it has come up with a Data type mismatch error when trying to run the union query.

Now I could understand if the data was coming from different tables, but the material field (data type = Text) used in the join in the query is from the same table.... I have checked and there aren't any blank records or anything else that looks out of the norm.

Anyway - here is some detail on the queries etc

Query 1 - Checked Operations
Code:
SELECT [tbl COOIS Ops].Order, [tbl COOIS Ops].Wk_Center, [tbl COOIS Hdrs].Material, [tbl COOIS Hdrs].Material_description, [tbl COOIS Hdrs].Target_qty, [tbl COOIS Hdrs].Delivered_qty, [tbl COOIS Hdrs].Basic_start, [tbl COOIS Hdrs].Basic_finish, [tbl COOIS Hdrs].System_status, [tbl COOIS Ops].Routings_Checked, [tbl COOIS Ops].Ignore_data, [tbl COOIS Ops].Std_Labour, [Act_Labour]+[OT_Labour] AS TotalActualLabour, Val(IIf([Delivered_qty]=0,Null,[TotalActualLabour]/[Delivered_qty])) AS ActOpQty, IIf([Ignore_data]=-1,Null,[ActOpQty]) AS ActOpQtyIgnored
FROM [tbl COOIS Hdrs] INNER JOIN [tbl COOIS Ops] ON [tbl COOIS Hdrs].Order = [tbl COOIS Ops].Order
WHERE ((([tbl COOIS Ops].Order) Not Like "6*") AND (([tbl COOIS Ops].Wk_Center) Not Like "*FLSK*" And ([tbl COOIS Ops].Wk_Center) Not Like "*REWORK*") AND (([tbl COOIS Hdrs].Delivered_qty)>0) AND (([tbl COOIS Hdrs].System_status) Like "TECO*" Or ([tbl COOIS Hdrs].System_status) Like "*DLV*" Or ([tbl COOIS Hdrs].System_status) Like "*CLSD*") AND (([tbl COOIS Ops].Routings_Checked)=Yes));

Query 2 - Unchecked Operations
Code:
SELECT [tbl COOIS Hdrs].Order, [tbl COOIS Ops].Wk_Center, [tbl COOIS Hdrs].Material, [tbl COOIS Hdrs].Material_description, [tbl COOIS Hdrs].Target_qty, [tbl COOIS Hdrs].Delivered_qty, [tbl COOIS Hdrs].Basic_start, [tbl COOIS Hdrs].Basic_finish, [tbl COOIS Hdrs].System_status, [tbl COOIS Ops].Routings_Checked, [tbl COOIS Ops].Ignore_data, [tbl COOIS Ops].Std_Labour, [Act_Labour]+[OT_Labour] AS TotalActualLabour, Val(IIf([Delivered_qty]=0,Null,[TotalActualLabour]/[Delivered_qty])) AS ActOpQty, IIf([Ignore_data]=-1,Null,[ActOpQty]) AS ActOpQtyIgnored
FROM [tbl COOIS Hdrs] LEFT JOIN [tbl COOIS Ops] ON [tbl COOIS Hdrs].Order = [tbl COOIS Ops].Order
WHERE ((([tbl COOIS Hdrs].Order) Not Like "6*") AND (([tbl COOIS Ops].Wk_Center) Not Like "*FLSK*" And ([tbl COOIS Ops].Wk_Center) Not Like "*REWORK*") AND (([tbl COOIS Hdrs].Material) Not Like "2UCX*") AND (([tbl COOIS Hdrs].Material_description) Not Like "Packing Assy*") AND (([tbl COOIS Hdrs].Delivered_qty)>0) AND (([tbl COOIS Hdrs].System_status) Like "TECO*" Or ([tbl COOIS Hdrs].System_status) Like "*CLSD*") AND (([tbl COOIS Ops].Routings_Checked)=No));

Union Query
Code:
SELECT [qry Routings - To Analyse].*
FROM [qry Routings - To Analyse];

UNION SELECT [qry Routings - Analysed].*
FROM [qry Routings - Analysed] INNER JOIN [qry Routings - To Analyse] ON [qry Routings - Analysed].Material = [qry Routings - To Analyse].Material;

Any help greatly appreciated
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 21:47
Joined
Aug 30, 2003
Messages
36,118
For starters, the semi-colon in the first SELECT clause would cause an error.
 

Darrell

Registered User.
Local time
Today, 04:47
Joined
Feb 1, 2001
Messages
299
Hi Paul,

This hasn't caused me a problem ever since I created it over a year ago.

I have a backup of this db which still works fine (just tested now) but has less data to work with so I'm thinking that it has to be the data. I just can't see where it would cause the problem....
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 21:47
Joined
Aug 30, 2003
Messages
36,118
Odd, because it always terminates an SQL expression in my experience. the queries run individually? I wonder, as you suspect, if there's something in the data causing this. I don't suppose you can post the db?

By the way, my daughter moved to Wellington early this year. We visited near Easter and during a weekend trip we tried to go to Napier but couldn't find lodging.
 

Darrell

Registered User.
Local time
Today, 04:47
Joined
Feb 1, 2001
Messages
299
Wellington... she'll be loving all the earthquakes then :)

Hmmm - Napier generally only fills up around Feb due to loads of stuff on... Next time you can just drop me a line and I'll help you out.

Anyway - Am going through the data at the moment. It's slow and painful but I'll see what pops out...

Cheers
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 21:47
Joined
Aug 30, 2003
Messages
36,118
They did get shaken up a bit, but no serious damage. Re Napier, it was last minute and Easter weekend, so no surprise we had trouble. Funny part was we went to Martinborough wine area, not realizing that it all shut down around Easter. Crazy Americans, but we had fun. Can't wait to return and see more of the country.
 

Darrell

Registered User.
Local time
Today, 04:47
Joined
Feb 1, 2001
Messages
299
Update - I found 3 production orders that were causing a problem here. If I marked them as 'checked' I get no error. Thing is, I can't see anything different to these production orders than any other data. The material number that links the queriers in the union query is not new and there are already hundreds of records which have this number.

The mystery remains.....
 

Users who are viewing this thread

Top Bottom