asked on

blank date field in union query

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
from Q_AllCommissionActivity
UNION Select ID, Balance, "Balance" as ActType, Date() as ActDate, Earliest
From Q_AllCommissionBalance
ORDER BY ID, ActDate;

Open in new window

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

the_bill

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BBlu

ASKER

when I use the Null, it doesn't add the Earliest date for the other table either. Choosing the actual null date works, but let me see: something else is going wrong.

HainKurt

did you try this?

SELECT ID, Activity, iif([Activity]<1,"Payment","Commission") as ActType,
       [Pmt or Comm Date] as ActDate, null as Earliest
  from Q_AllCommissionActivity
UNION 
Select ID, Balance, "Balance" as ActType, Date() as ActDate, Earliest
  From Q_AllCommissionBalance
 ORDER BY ID, ActDate;

Open in new window

SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BBlu

ASKER

thanks to all of you. Using the null, but putting it as the second part of the union worked.