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

BBluAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
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;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
the_billCommented:
Hi,

You can use the null date, '1/Jan/1900' as the empty date, then order or filter them out

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, '1/1/1900' as ActDate, Earliest
From Q_AllCommissionBalance
ORDER BY ID, ActDate;
0
BBluAuthor Commented:
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.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

HainKurtSr. System AnalystCommented:
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

0
LowfatspreadCommented:
switch the order of the unions so that the select with earliest is first ... so that a Date column type is established....

then use NULL as Earliest in the other select  as a placeholder for thedate..

0
BBluAuthor Commented:
thanks to all of you.  Using the null, but putting it as the second part of the union worked.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.