How sort by latest of two date columns ?

[Guest]

I have a query that returns 2 date colums, target date and actual date.
Either or both may be null. I need a query to load a list box and it must be
sorted in ascending date order with the blank dates last. For sorting the
dates the actual date if present overrides the target date. To sort I figure
I can sort by CDATE(NZ(MyDate,"12/31/2999") to make the blank dates last. But
how do I get the latest of the target and actual dates? Can I say
IIF(ActualDate > TargetDate,ActualDate,TargetDate), will this work with dates
and nulls?

 

[Guest]

Try sorting by this

cvdate(IIF (NZ([target date],"12/31/2999") > NZ([actual
date],"12/31/2999") , NZ([actual date],"12/31/2999") , NZ([target
date],"12/31/2999")))

 

[John Spencer (MVP)]

How about

CDate(NZ(NZ(ActualDate,TargetDate),#12/31/2999#))

Sorts by ActualDate, then Target Date (if no actual), then "null" Date if both
are null.
That is one way you said you wanted to sort.

The other method

CDate(NZ(IIF(NZ(ActualDate,#1/1/1899#)>Nz(TargetDate,#1/1/1899#),ActualDate,TargetDate),#12/31/2999#))

Sorts by the greater of ActualDate and TargetDate or by "null" date if both are
null which is the other way you wanted to sort.