Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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)
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    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
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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