Consider the following simplified query that demonstrates my problem:
Code:
select t1.a, null as b, t1.c from someTable t1
UNION ALL
select t2.a, t2.aDateValue, t2.c from someOtherTable t2.
What appears to be happening is that Access assigns Short Text type for the generated column. The rows from the first query contain null as expected, but the date values from the second query appear as strings of random Unicode characters.
The rows with values in orderDate come from the first query, while the ones with the (mostly) CJK strings come from the second, where the column was a Date/Time.
I suspect Access sees a NULL value first, and decides the column is ShortText, then when it sees a date value it just interprets the binary Date/Time value as UTF-8.
How do I tell Access that the second column is a Date/Time?
I tried CDate(null) but Access complains "Invalid use of null"