Union Of Good Queries Giving A Union Query - "Type Mismatch"

[dominicdunmow]

I have 2 queries that are exactly the same (although based on different tables so datasheet is different but in same format) - they work perfectly as standalone queries.

When I add them together in a Union Query I get an "Type Mismatch" error message back.

I'm baffeld as to how the queries can can work individually (and be identical) but not when part of a Union.

Another peculiarity with the database is that the font keeps changing of its own volition.

Can anyone help?

 

[PHV]

Are the columns returned by the 2 queries of the same type respectivly ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dominicdunmow]

Yes, everything about the queries is identical - I used one as a template to make the other, just pointed it to a different table.

 

[gojohnnygogogogo]

make sure the fields have the same name, also replace nulls.

 

[dominicdunmow]

All the fields have the same name (one query was derived from the other)

There are no nulls.

I wondered whether there were any other peculiarities that are singular to Union queries as I have fallen foul of one before - that a Select qury will return 2 records exactly the same (without a primary key) but a Union query won't.

 

[gojohnnygogogogo]

try using union all instead of union

 

[dominicdunmow]

Thanks,

...just tried Union ALL, it gives a different outcome - the same Mismatch meesage, but now takes me to the datasheet, where every record reads "#name?"

Does that ring any bells?

 

[gojohnnygogogogo]

can you post your sql ?

 

[dominicdunmow]

The SQL is huge - there are 41 fields on both queries.

After first getting the Data Type Mismatch, I've reworked the queries using each other a a template, so I'm pretty sure there would be nothing wrong with the SQL.

 

[gojohnnygogogogo]

not sure then mate.
can you create a similar query with only say 5 fields, does that bring back any data.

 

[dominicdunmow]

I tihnk I've cracked it, your last suggestion kicked mew into life.

I started removing a field at a time from both queries until the Union worked. Not surprisingly it is a date field that is causing the problem. I can't think how but there must be something untoward with it.

Thanks for the inspiration.

 

[gojohnnygogogogo]

glad to here it.
hope you can sort out the date field.
theres no invalid date in there ?