I'm trying to create a union query, combining a table and another query. One of the fields/columns in the new "table" will be a date field, but I only want to pull this value from one of the unioning tables. How do I make that blank for records from the one table without causing an error when I'm doing any comparisons in future queries.
I've attached the code
The column/fied in question is the new "Earliest" field.
SELECT ID, Activity, iif([Activity]<1,"Payment","Commission") as ActType,[Pmt or Comm Date] as ActDate, "" as Earliest
UNION Select ID, Balance, "Balance" as ActType, Date() as ActDate, Earliest
ORDER BY ID, ActDate;