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...
formula help in a query
-
- 2StarLounger
- Posts: 170
- Joined: 05 Feb 2010, 18:04
- Location: Jacksonville, NC
formula help in a query
You do not have the required permissions to view the files attached to this post.
Last edited by teachesms on 29 Jul 2010, 20:47, edited 1 time in total.
If you can't convince them, confuse them - Harry S. Truman
Nannette
Nannette
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: formula help in a query
Hello.
If a date field is empty it is equivalent to a null value. Try replacing <>"" with 'is not null' and "" with 'null'.
Andy.
If a date field is empty it is equivalent to a null value. Try replacing <>"" with 'is not null' and "" with 'null'.
Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78789
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: formula help in a query
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.
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.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 170
- Joined: 05 Feb 2010, 18:04
- Location: Jacksonville, NC
Re: formula help in a query
PERFECT!!!!!
You the man! You too Andy!
Thank you Hans...it was driving me nuts!
You the man! You too Andy!
Thank you Hans...it was driving me nuts!
If you can't convince them, confuse them - Harry S. Truman
Nannette
Nannette