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

    Time in a Query (Acces 2000)

    I have another famous MS Access 2000 question. In Queries when your fields
    include Date/Time and you want to subtract one field from the other with
    hours, minutes, and seconds. How do you convert the hours to minutes? In
    MS Excel you would simply type =(G18-G17)*60. G18 being the End Date/time
    and G17 being the Start Date/Time. The total time period is in minutes.
    Example:
    On 11/01/2001 05:00 (G17) and 11/05/2001 07:00 (G18) = 5880:00:00 (G19).
    I want to perform this same formula in MS Access using the query with (2)
    fields each having Start Date/Time and End Date/Time and the third field
    being the expression formula.
    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
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Time in a Query (Acces 2000)

    If you want to know how many minutes, you can simply do an expression in the third column of the query which looks something like:
    MinutesDif = StopDateTime(G18)-StartDateTime(G17)*24*60
    This works because the part of a date/time field to the right of the decimal is a fraction representing the part of the day from midnight that the current time represents. You can also format it as a time, and it will do the conversion to hours, minutes and seconds for you. You can also mess around with the various date/time functions, but that's a fair bit more involved. Hope this is
    Wendell

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Time in a Query (Acces 2000)

    Hi,
    If you only want the number of minutes between the two (rather than hours, minutes and seconds or whatever), you could simply use:
    MinsElapsed: DateDiff("n",[start_date],[end_date])
    in your query.
    Hope that helps.
    (Incidentally, if you need to use the Help on the DateDiff function, you'll get more information if you look it up in the VBEditor Help than if you do it in the database window Help)
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Time in a Query (Acces 2000)

    Wendell,

    Thank you so much. Always so nice to come here, and get such excellent help from folks who really care enough to answer. You are great.

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

    Re: Time in a Query (Acces 2000)

    Rory,

    Thankyou for your help. The DateDiff function works well. Have used the Between[type start date] And [type end date] before and this is very similar.

    Nannette
    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>

Posting Permissions

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