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