Struggling with IIf expression with date in query

[DelbyJones]

I have a query that I want to return "due by 9/8/14" if the [Grace] field is blank or if the date in that field is before 9/8/14. If the date in the field is after 9/8/14, I want it to return "valid through (the date in the field)"



I wrote: MedWords: IIf([Grace]=Null,"due 9/8/14",IIf([Grace]<9/8/14,"due 9/8/14","valid through " & [Grace])) and all I get is "valid through" and either blank, or whatever date is in the [Grace] field.

Can someone tell me what I am doing wrong?

[June7]

Are you in the U.S.? Is that example date for Sept 8, 2014?

Cannot use =Null.

Try:

IIf(IsNull([Grace]) Or [Grace] < #9/8/14#, "due 9/8/14", "valid through " & [Grace])

or

IIf(Nz([Grace],#1/1/1900#) < #9/8/14#, "due 9/8/14", "valid through " & [Grace])

Problem with this code is you will have to remember to modify it every year.

[Xarkath]

try this:

IIf(IsNull([Grace]),"due 9/8/14",IIf([Grace]<#9/8/14#,"due 9/8/14","valid through " & [Grace]))

[DelbyJones]

YES! It worked! Thank you!!! I can change the date yearly, that is NO problem. Again, thank you SO much. You just made my year!