Results 1 to 3 of 3
  1. #1
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    DateAdd for Hours is not always precise

    I won't bore you with the details, but I am able to prove that using the DateAdd() function with the "h" option is not always precise!

    You can verify this yourself. In the Immediate Window just enter this:

    ?DateAdd("h",1,#08:00#) = #09:00#

    This adds 1 hour to 8:00am and compares the result to 9:00am. As expected, this returns TRUE. But then try this:

    ?DateAdd("h",2,#08:00#) = #10:00#

    This returns FALSE!

    But wait, it gets better! I had populated a table of TimeSlots using an append query that utilized the DateAdd function. The PrimaryKey was the TimeSlot field, which was a Date/Time field. In looking at the table, there was an entry for 10:00am. I could manually add another record for 10:00am, so that now my table shows 2 records at 10:00am (the Primary Key)! Actually, I even expanded the formatting to show 10:00:00am for both cases.

    Just so you won't think this is insignificant, I discovered this when I wrote a query to return all TimesSlots >=#10:00#. The 10:00am timeslot was NOT returned, it started at 10:15am.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I believe the root cause of this is the way dates are stored in Access. Since you are using hours, that is stored as a fraction in floating point format, and 1 hour rounds up nicely, but 2 hours rounds down when you do it that way, and the last bit is different when you add two hours compared to how it is set if you explicitly set the hour. Makes things a bit challenging for sure.
    Wendell

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Yes, it is how Dates are stored that is the problem. That's why I seldom define my numeric fields as singles or doubles; I use currency unless I have a requirement for more than 4 decimal places. I have determined that I can use the TimeSerial() function to form the new time that is compatible with a manual entry. So in my example of adding 2 hours to an existing field (lets call it oldTime), I'd use this instead:

    TimerSerial( DatePart("h", oldTime) + 2, DatePart("n", oldTime), DatePart("s", oldTime))

    A bit of a pain, but at least I'll get consistent values.

    -- added later --

    Here is a better way: TimeValue(DateAdd("h", 2, oldtime))
    Last edited by MarkLiquorman; 2011-10-13 at 21:48. Reason: Added a better way
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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