formula help in a query

[teachesms]

this is in relation to the excel formula i posted earlier. however, i have convinced the person i'm trying to help to do the formula in access, instead of trying to send it to excel and then back again...

I am attaching an example db with the formula:

Days Past Delivery Date: IIF([ACTCOMP DATE]<[REQCOMP DATE],IIF([REQCOMP DATE]<>"",[REQ COMP DATE]-[ACT COMP DATE],""),0)

within a query...I haven't got it right. I need: the amount of days the "Act comp date" has exceeded the "req comp date" and of course, nothing if it doesn't meet that criteria

In my Excel sheet it looks like the pic I am attaching. In Access the above formula (which references the field names instead of excel cell addresses) is not working and giving me an error in the result.

Can you troubleshoot the error? I FIXED ONE AFTER POSTING, but it still gives me an error...

[agibsonsw]

Hello.
If a date field is empty it is equivalent to a null value. Try replacing <>"" with 'is not null' and "" with 'null'.
Andy.

[HansV]

You had the subtraction the wrong way round, apart from that it's much simpler than you thought:

Days Past Delivery Date: IIf([ACT COMP DATE]<[REQ COMP DATE],[REQ COMP DATE]-[ACT COMP DATE],Null)

An empty value in Access is not an empty string "" but Null.

[teachesms]

PERFECT!!!!!

You the man! You too Andy!

Thank you Hans...it was driving me nuts!