Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Leamington Spa, Warwickshire, England
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    time diference query (Access 2007)

    My database has a start time and end time for exams - i need to find out how long a person took (i.e. 10 minutes or 45 minutes would be the max).

    I earlier versions of access I would have created a query with =[EndTime]-StartTime] to get the difference.

    I tried this in access 2007 and get odd formats for example if the data is
    [11:15]-[11:00] i get answers in the format 1.1,

    the fields in the original table are in time format, there is no way i can see of formatting this field in the quesry.

    Any help greatly appreciated.

    Also,the database was originally created in Office XP, converted to 2003 and finally 2007

    Regards

    Michelle

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: time diference query (Access 2007)

    Try a function called DateDiff

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: time diference query (Access 2007)

    When I do his in 2007, I get 0.0104166666666667, using times of 11:00 and 11:15. Now this is actually the right answer, but it looks wrong.

    Dates and times are stored as numbers with the whole numbers being the days, and the dec points being the times. so 0.0104166666666667 of a day is 15 mins.


    If you multiple by 24 Elapsed: ([endtime]-[starttime])* 24 you get an answer in hours .25 which is also correct.

    Alternatively, don't put in the *24, but format the field as hh:nn (or short time) you will get 0:15 which is also correct.

    To format the field, display the properties box, click in the field, then type in the format.
    Attached Images Attached Images
    Regards
    John



  4. #4
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Leamington Spa, Warwickshire, England
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Unfortunatley in the query I can only choose from

    General Number
    Currency
    Euro
    Standard
    Fixed
    Scientific
    percentage

    There is no time element in the query field

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can't select Short Time (or any other date/time format) from the dropdown list, but you can type Short Time in the Format property. It will be accepted and it should work correctly. You can also type a custom time format in the Format property, for example h:nn.

Posting Permissions

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