Union Query data types

[Curry]

Hi All,

I have a Union query that brings together several tables into one. This works perfectly well however there is an occassion when a field in one table has its data type changed to text from date. This is to allow N/A to be entered rather than a date. When this occurs the union query changes all the dates from the other tables to text also and therefore other queries I am running based on the Union query now fail.

Is there any way to have the Union Query ignore the data from the table where and when this this occurs as it is not necessary to show in the query and can happen with any of the tables at a users discretion

Thanks Again
IC

 

[ajetrumpet]

Curry,

I wonder if it is possible to use a combination of the conversation function and the IIF statement to get the desired result you need? Say like (for fields that contain dates, but are being converted to text to allow universal compliance)...

IIF([field] IS NULL (or = "N/A"), NULL, CDate([field], Format Here))

there is an occassion when a field in one table has its data type changed to text from date.

I'm not surprised that this is happening; it only makes sense.

The program probably converts the data in such a way to which all values involved can be evaluated. E.G. - 1/1/2007 can be read as a text string as well as a date, but going the only way: N/A can be read as a text string, but not a date!

 

[Curry]

Thanks for that....This helped me a lot.
IC