Need Help Writing Query Expression that Includes IsNull Or with dates

[Adelestrat]

I'm trying to have a query return "due by 9/8/14" if the entry in a field is blank or has a date before 9/8/14, or return "valid through (date)" if the date in the field is after 9/8/14. Here's how I've written the expression:

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

What am I doing wrong?

[Adelestrat]

Never mind, I figured it out! It works when I wrote it this way:

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

[NeoPa]

Try this :

MedWords=IIf(Nz([Grace],#1/1/2014#)<#9/8/2014#,'due 9/8/2014','valid through '+[Grace])

[Adelestrat]

Thanks, NeoPa. That didn't work. As I mentioned in my second post, I finally found the right way to do it which was:

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

[NeoPa]

Please share how it didn't work. If all the details we have from you are correct then I fail to see how that were possible.

Your approach certainly should work too - but I wouldn't describe it as the right way. You certainly wouldn't use IsNull() in SQL for preference over [X] Is Null. Other than that I would generally look for a single check rather than two if possible.

I'm very interested to hear how it is that my suggestion failed mind you.

[Adelestrat]

I see that my expression builder was trying autocorrect your expression (by putting MedWords into brackers). When I manually return yours to what you actually wrote, it does work.

Thanks so much.

[NeoPa]

No problems :-)

Don't forget - your version works also. There is rarely ever only one right answer.