Results 1 to 6 of 6
  1. #1
    Pauld is offline Novice
    Windows Vista Access 2000
    Join Date
    Sep 2011
    Posts
    3

    DateDiff returning negative

    Here's what I'm trying to do..

    HoursRan: DateDiff("h",[Field1],[Field2])

    Both fields are date/time that cover a 24 hr period,
    for eample some records equal field1=12:00am, field2=3:00am which = 3, these are ok, but alot of times they equal field1=3:00am, field2=12:00am these are the ones I'm having a problem. instead of equaling 21 they are equaling to -3.
    Field1 is a start time that always starts at 12:00am on current day
    Field2 is an end time that ends every morning at 12:00am on next day

    Thanks for any input you can provide..

  2. #2
    pdebaets is offline Competent Performer
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Los Angeles
    Posts
    235
    You may be inadvertently saving the time in field1 and field2 instead of the DATE and time.

  3. #3
    CraigDolphin is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Apr 2009
    Location
    Custer, WA, USA
    Posts
    125
    To elaborate:

    Datediff requires that the earliest date/time is in field 1, and the most recent date/time is in field 2. Order is important.

    Access also stores dates/times in a particular way, and assumes that midnight is actually the start on a new day (not the end of the day).

    A date is stored as the integer portion of a number that represents the number of whole days that have elapsed since December 30, 1899.

    A time is stored as the decimal portion of the number, and .0 represents midnight, with the actual value after the decimal corresponding to the number of seconds since midmight divided by the total number of seconds in a day.

    A date/time combines both parts into a single value. And both dates and times are actually datetimes.

    Thus,
    -midnight on December 30, 1899 is represented by the value 0.0
    -midday on December 30, 1899 is represented by the value 0.5
    -midnight on December 31, 1899 is actually represented by the value 1.0
    -6 PM on Decmber 31, 1899 is represented by the value 1.75
    and so on.
    -times without dates are actually stored as 0.<whatever> so they actually are stored as times on December 30,1899. eg, a 'time' of 3 AM would be stored as the value 0.125

    So, when you are asking datediff to tell the number of hours that have elapsed, starting at 3am and ending at 12AM (midnight) what you are actually requesting is:

    How many hours have elapsed starting at 3:00 am (on December 30, 1899) and ending at midnight (on Decmeber 30, 1899).

    Recall that midnight is actually the START of the day, not the end, and that datediff expects the earlier date/time to be in the first field, and you'll see why you get the funny result.


    The way I approach this is either to explictly store both the date and the time together, or to use a custom code function to perform some simple logic on times.

    Code:
    Public Function NoOrder_DateDiff(intType As String, datetime1 As Date, datetime2 As Date) As Single
    If datetime2 < datetime1 Then
        NoOrder_DateDiff = DateDiff(intType, datetime2, datetime1)
    Else
        NoOrder_DateDiff = DateDiff(intType, datetime1, datetime2)
    End If
    End Function
    Which essentially just swaps around the field values so that the lowest value is always passed to the datediff function first.

    Thus :
    NoOrder_DateDiff("h",#3:00 AM#,#12:00 AM#) yields a value of 3
    as does:
    NoOrder_DateDiff("h",#12:00 AM#,#3:00 AM#)

  4. #4
    Pauld is offline Novice
    Windows Vista Access 2000
    Join Date
    Sep 2011
    Posts
    3
    Yeah I have tried reversing them, which still only produces 3 instead of 21. Maybe I can explain it a little better..

    At top of the form I have a date field set to long date..
    Then I have the start field and the end field both set to medium time..
    (It does what I want it to if the time fields are set to general, but I don't want to enter the date and time every time.)

    I hoping a function can be written that will work pretty much like your example. Except when field1 < field2 it looks to the date field, calls for the next day then applies that date to field2.

    I've even tried DateDiff("h",field1,field2)+1
    Field1=3:00am, Field2=11:59PM which equals to 20 then adds one to make it 21 which works, but the problem with that is the fields that don't need 1 added end up having 1 too many.

    Thanks again

  5. #5
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I tested this with the two time examples you provided and it seemed to return the correct results. Try this:

    Code:
    HoursRan: DateDiff("h",[Field1],[Field2])+((DateDiff("h",[Field1],[Field2])<0)*-24)

  6. #6
    Pauld is offline Novice
    Windows Vista Access 2000
    Join Date
    Sep 2011
    Posts
    3
    Thanks works perfect... Seems so simple when you see it..LOL
    Thanks again...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. need help with formatting negative numbers
    By orientalmed in forum Access
    Replies: 3
    Last Post: 03-06-2013, 01:24 PM
  2. Converting Positive to Negative
    By TheProfessorIII in forum Access
    Replies: 5
    Last Post: 03-22-2011, 06:38 AM
  3. Fixing Negative Autonumbers
    By prophecym in forum Access
    Replies: 5
    Last Post: 02-03-2011, 03:48 PM
  4. Summing Positive and Negative Currency
    By nweird in forum Reports
    Replies: 1
    Last Post: 07-22-2010, 10:05 AM
  5. Sum results give me negative values !
    By Costa in forum Reports
    Replies: 4
    Last Post: 03-03-2010, 12:58 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums