Multiple Iif in Query Builder

[zooropa66]

I have the following field in Query Builder

Status: IIf(DateValue(Now())>DateValue([Expiry Date]),"Expired","Current")

and it works fine

What I would like, however, is to include the following condition into the above

Status: IIf(DateValue([Expiry Date] - DateValue(Now()) < 2),"Nearly Expired","Current")

so in effect the status would be either current, nearly expired or expired

Can anyone help? Thanks

 

[jdraw]

Please see
http://www.techonthenet.com/access/functions/date/datediff.php

Good Luck.

 

[zooropa66]

Thanks for your reply. I had a look at the link and i'm no further forward. I think you might not have read my question correctly or i haven't explained clearly. I know how to put the two expressions into 2 separate fields in query builder. I want to be able to incorporate them into 1 field!

 

[Simon_MT]

Try:

IIf(DateValue(Now())>DateValue([Expiry Date]),"Expired",IIf(DateValue([Expiry Date] - DateValue(Now()) < 2),"Nearly Expired","Current"))

Simon

 

[Pat Hartman]

You can simplify the expression by using Date() instead of Now().
IIf(Date()>DateValue([Expiry Date]),"Expired",IIf(DateValue([Expiry Date]) - Date() < 2),"Nearly Expired","Current"))

 

[zooropa66]

Thanks for the replies. If I enter the following, the Expired ones show up but the second Iif just returns #error

Expr1: IIf(DateValue(Now())>DateValue([Expiry Date]),"Expired",IIf(DateValue([Expiry Date]-DateValue(Now())<2),"Nearly Expired","Current"))

 

[zooropa66]

I got it. Just a small tweak required. Here it is.

Expr1: IIf(DateValue(Now())>DateValue([Expiry Date]),"Expired",IIf(([Expiry Date]-Date()<2),"Nearly Expired","Current"))

Thanks for your help everyone

 

[Simon_MT]

Try this:

Test: IIf(DateValue(Now())>DateValue([Expiry Date]),"Expired",IIf(DateDiff("d",[Expiry Date],Now())>2,"Nearly Expired","Current"))

Simon