IIF query statement with two dates

[mick3911]

I am trying to create a field in a select query called ‘Status’ which will be populated depending on two date fields.



The dates fields are ‘ClosedDate’ and ‘VerifiedDate’.

The statement I am trying to get is;

If ClosedDate and VerifiedDate are blank, then return ‘Open’.

If ClosedDate contains a date and VerifiedDate is blank, then return ‘Closed’.

If both ClosedDate and VerifiedDate contain dates, then return ‘Verified’.

I have tried the following but keep getting an invalid syntax message.

Status: IIf(IsNull([ClosedDate] AND [VerifiedDate] , "Open"),IIf [ClosedDate]<=Date() AND (IsNull([VerifiedDate], "Closed", "Verified")

Can someone please point me in the right direction to solve this?

Many thanks

[June7]

Status: IIf(Not IsNull([VerifiedDate]), "Verified", IIf(Not IsNull([ClosedDate]), "Closed", "Open"))

[mick3911]

Hi June7,

That works great. Simple really when you read it.

I didn't know about 'Not IsNull'

Thank you for your help.