date issue IF THEN???

[ThaiByThai]

Hi -

I have a table with 2 different dates fields - and i can't get it to show me me the date

I would like to get the query to run something like this --

IF Date1 is GREATER then date2 use date1 IF date1 is LESS then date2 use date2 if there is no date1 use date2

right now it's only reading from date1 - i'm not sure how to do the IF THEN ...

this is what i have now but it's only reading Date1...

CheckDate: DateAdd("m",-6,DateAdd("yyyy",5,[Date1]))

 

[JamesMcS]

Nest some iifs, I reckon:

iif(date1 is null,date2,iif(date1>date2,date1,iif(date1<date2,date2,date1)))

 

[JamesMcS]

Doesn't trap if date1=date 2 though. Might be worth considering. You can nest loads of iifs...