IIF and IsNull

[mlubbs]

I am trying to figure out a formula using the iif and isnull function.

I am doing an on time report that looks at the Date Received, Date Required, Ship Date, Todays date and a few other things.

The formula I am trying to work with is

DAYS WORK DONE IN: IIf(IsNull([SHIP_DATE]),[TODAY]-[REQUIRED]+1,[SHIP_DATE]-[RECEIVED]+1)

What I am trying to get it to do is if the ship is empty subtract the todays date from the required date and add 1. If the ship date has a date subtract the ship date from the received date and add 1.

When I run the query I just get #Error.

Is there an easier way to do this or do I just have an error in my code?

Thanks for your help

Mike

 

[CraigDolphin]

You might be having a datatype problem...make sure that the datatype for the date fields is actually date/time, not text or sumesuch.

Here's an alternative way to write it that makes use of the datediff function.

DAYS WORK DONE IN: IIf(IsNull([SHIP_DATE]),Datediff("d",[TODAY],[REQUIRED])+1,Datediff("d",[SHIP_DATE],[RECEIVED])+1)

 

[mlubbs]

That worked like a charm! Thank you very much!!!

 

[CraigDolphin]

Glad I could help