Return Earliest Date

[Guest]

I have a query with the following fields:

dateA, dateB, dateC, dateE, dateF
1/1/05, , 2/1/05, 6/15/05, 12/5/04
, 9-15/05, 6/4/04, , 4/15/05
6/15/05,


In my last query column I'm trying to write an Iif statement in an
expression to return the earliest date of dateA, dateB, dateC, dateE or
dateF. I'm running into trouble where there are dates that are null. How
can I write the Iif statement that will return the earlies date? Thank you
very much.

 

[[MVP] S.Clark]

Use Nz(). Make the return parameter a date way into the future.

Nz(fieldname, #12/31/2999#)

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

 

[Chaim]

What does the query look like? The fact that you want to find the earliest
of these dates implies that they are all represent the same event and you
wish to determine the earliest occurence. If these were the dates that a
fish was caught, a meteor missed the space station, a payment was made to a
store, etc., what would be the meaning of the earliest of these dates?

An IIF expression is going to be a mess. There will be some very deep
nesting to work through 5 dates like this. To say nothing of dealing with
the NULLs. Although you could wrap all of your dates in something Nz(dateA,
#12/31/9999#) which will replace the NULL dates with the largest allowed
date. So at least that small piece of the problem would be avoided.

It's much easier to work on columns than rows like this. It looks like
something is wrong or needed information is missing here.