sql - How to query earliest date from several possibly blank date fields? -

given following table:

+----+----------+-----------+----------+ | id |  date1   |   date2   |  date3   | +----+----------+-----------+----------+ |  1 | 3/2/2013 | 5/6/2013  |          | |  2 |          | 12/1/2011 | 6/5/2010 | |  3 | 1/1/1936 | 1/5/1936  | 1/9/1945 | |  4 | 2/1/2014 |           |          | +----+----------+-----------+----------+ 

i want query returns earliest date in each row. @ least 1 of date columns populated.

i've tried:

select id,  iif(date1<date2 , date1<date3,     date1,     iif(date2<date1 , date2<date3,         date2,         date3)) dateearliest tbl; 

but seems returns correct result if date3 earliest; otherwise returns blank.

you can't compare against null value. use nz function convert nulls value can compare to. syntax nz is: nz ( variant, [ value_if_null ] ).

so you'll use this:

iif(nz(date1,#1/1/2999#) < nz(date2,#1/1/2999#) , nz(date1,#1/1/2999#) < (nz(date3,#1/1/2999#) 

if being used in access, , know vba, create function returned value want. may neater like:

select id, lowdate([date1],[date2],[date3]) dateearliest 

here's function should work you.

function lowdate(d1, d2, d3)    d1 = nz(d1, #1/1/2999#)    d2 = nz(d2, #1/1/2999#)    d3 = nz(d3, #1/1/2999#)    if d1 < d2 , d1 < d3       lowdate = d1    elseif d2 < d1 , d2 < d3       lowdate = d2    else       lowdate = d3    end if end function