DateDiff IIF criteria

[Bangorgav]

Hi guys, having a bit of trouble at the minute with a new query,

I have 4 colums

DATE 1 DATE 2 DAYS Days with reviewer

i need a query to check

if DATE 2 = blank,(if true Datediff between Date 1 and now in days),(if false, DateDiff between Date 2 and now in days)

Cheers guys

Tried

Expr2: IIf([date2]="",DateDiff("d",[date2],Now()),DateDiff("d",[date1],Now()))

But its only giving me the date difference for some fields, i don't think the False statement is working..

Much appreciated.

 

[pr2-eugin]

I am not sure how your data is organised.. Based on your code, you are checking if Date2 is empty, then you are trying to find the date diff of the empty date? So try this instead..

Expr2: IIf([COLOR=Blue]Nz[/COLOR]([Date2],"")="", DateDiff("d",[Date1],[COLOR=Red]Date()[/COLOR]), DateDiff("d",[Date2],[COLOR=Red]Date()[/COLOR]))

The changes I have made are..
* Used a Nz() function to test for Null values in the date field.
* Turned the True and False parts of the IIf statement.
* Used a Date() rather than using a Now()

Also a suggestion please change your field names to something that will be meaningful rather than Date1 or Date2..