Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    2

    Unanswered: DateDiff in Access 2000

    My database is for an emergency services system. I use the datediff function to calculate time: in-route, on scene, at hospital. This problem I have is when the clock goes over 24-hours. For example, the dispatch time is 23:50, and the at hosptial time is 00:12. The query returns a negative number, such as -1406. The query works fine for all other times. I would appreciate any help or advice anyone may have.

    Here is the query:
    Expr1: DateDiff("n",[Dispatch],[At Hospital / Clear])

    Expr2: IIf([Expr1]<"0",[Expr1]+"1440",[Expr1])

    Thank You

  2. #2
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150
    I assume that you are using time only. It may be a better Idea to include the date as well as the time.
    JJ Kennedy
    Double J IT Solutions
    www.doublejit.co.nz

    VB 6, VB.NET, ASP.NET, MS SQL Server, MySQL, MS Access

  3. #3
    Join Date
    Aug 2003
    Posts
    2
    Originally posted by machado
    I assume that you are using time only. It may be a better Idea to include the date as well as the time.
    I have done it both ways, with and without including the date. The end result is the same.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    try...

    dim date1 as date
    dim date2 as date
    dim dateD as date
    dim float as double

    date1 = some valid date/time just before midnight
    date2 = another valid date/time a few hours later just after midnight
    dateD = date2 - date1
    float = date2 - date1
    msgbox "dateD: " & dateD & vbcrlf & "float: " & float

    e.g. (non-US dd/mm/yyyy format)

    date1 = 11/11/2002 23:30:00
    date2 = 12/11/2002 01:30:00
    returns:
    dateD: 02:00:00
    float: 0.0833333333358

    which is GREAT, but...

    date1 = 11/11/2002 23:30:00
    date2 = 24/11/2002 01:30:00
    returns:
    dateD: 11/01/1900 02:00:00
    float: 12.0833333333358

    A stores dates as 8-byte floats ("double") with the after-the-decimal part as fractional days, so you can take this sort of liberty if you are careful how you handle the before-the-decimal stuff.

    izy

Posting Permissions

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