Solved Calculated Field 3 conditions (1 Viewer)

[Lochwood]

I am looking for help with the syntax for 3 conditions in my query. Basically if [Date] is Null the calculated field called [Status] will be "NO" if [Date] between Date() and Date() +30 "EXP" and if [Date] >Date()+30 "YES"

Here's what I've done but can only get it to display the first condition "NO" on null fields

Status: (IIf([Expiry] Is Null,"NO") & IIf([expiry] Between Date() And Date()+30,"EXP" & IIf([expiry]>Date()+30,"YES")))

Any help appreciated

 

[Minty]

You shouldn't use the & in your expression, try

Status: (IIf([Expiry] Is Null,"NO", IIf([expiry] Between Date() And Date()+30,"EXP" ,"YES"))

Is Expiry ever < Date() ?

 

[Lochwood]

You shouldn't use the & in your expression, try

Status: (IIf([Expiry] Is Null,"NO", IIf([expiry] Between Date() And Date()+30,"EXP" ,"YES"))

Is Expiry ever < Date() ?

You've caught my fatal flaw. yes if date <date() then it is also EXP

 

[Minty]

Okay, so we need to switch that around to test for it.

Status: (IIf([Expiry] Is Null,"NO", IIf([expiry] > Date()+30,"YES" ,"EXP"))

Does that work? (I haven't had enough coffee yet...)

 

[Gasman]

I would have thought we would need

Status: (IIf([Expiry] Is Null,"NO", IIf([expiry] < Date()+30,"EXP" ,"YES"))

as we were looking for between and now less than ?

Edit: It is me that needs the coffee, I can now see it is the same thing.
I was fixated on the o/p original syntax.

 

[Lochwood]

Okay, so we need to switch that around to test for it.

Status: (IIf([Expiry] Is Null,"NO", IIf([expiry] > Date()+30,"YES" ,"EXP"))

Does that work? (I haven't had enough coffee yet...)

 

[Lochwood]

Genius.. Thanks Minty works perfect.. your caffeine count is fine