Thread: Date Formula problem (Access 2000)

1. Date Formula problem (Access 2000)

If I have a formula that figures dates that a class has ran (figuring employees hours * payrate) and that employee has worked no more then 1 day, my formula below keeps giving me minus one (-1) and money owing instead of due the employee, could someone help me on this, I want the employee who has worked just one day or less then 1 day to have 1 placed into their answer cell.

Days: IIf("h",[dtEnd Date]-[dtBegin Date]<=1,1)

2. Re: Date Formula problem (Access 2000)

The IIf function has the following syntax:

IIf(condition, result_if_true, result_if_false)

so "h" would be the condition, [dtEnd Date]-[dtBegin Date]<=1 the result_if_true, and 1 the result_if_false. That doesn't make much sense. Try

Days: IIf([dtEndDate]-[dtBeginDate]<1,1,[dtEndDate]-[dtBeginDate])

3. Re: Date Formula problem (Access 2000)

Am attaching the database Hans. That formula makes sense to me, so I used it, but now it is cutting off a day on the other Instructors who "do" have more then (1) day of class teaching time. Would you take a look at the querys design view? I enter 09/01/05 and 09/30/05 for the criteria when running it...and then check the day formula within the query.
thanks

4. SOLVED (Access 2000)

Sorry, I think I got it...I just edited the formula to read:

Days: IIf([dtEnd Date]-[dtBegin Date]<1,1,[dtEnd Date]-[dtBegin Date]+1)
And that did it.

Thank you so much Hans

5. Re: SOLVED (Access 2000)

Hi Nancy,

I don't usually wander through the access forum, and I've just come across this thread.

It seems to me your entire calculation need be no more than:
Days: [dtEnd Date]-[dtBegin Date]+1

Cheers

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•