Format NULL as date in Union Query

[Krysti]

Hopefully this is a question with an easy answer. I created a union query from two tables. One table has a field [DateTerminated] and the other table does not. I put in NULL for that field. However, the data type is now no longer formatted as a date and I need it to be. Is there a way to format the NULL as a date? Here is my code:

SELECT *
FROM [qryAGENTSTERMCT]

UNION ALL SELECT [AgentNo],[AgentAlpha],[Region],[District],[StartDate],NULL
FROM [qryAGENTSCT];

The problem I run into is when I run a macro to open a report with a where condition using dates, it does not filter the records properly because it is not recognizing the field as a date type. I also cannot format it as date in the report.

Any help would be greatly appreciated.

Thank you,

Christy

 

[neileg]

Can you add a calculated field to qryAGENTSCT, call it [DateTerminated], format as a date but set it to 0. I think this might work.

If it doesn't, a zero length text field, i.e. DateTerminated:"" might work.

Using a NULL to pad out the table list is asking for trouble.