Results 1 to 4 of 4
  1. #1
    dr4ke is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    108

    Difference between Dates/Times

    I currently have 4 colums that I need to manipulate to give me an 'elapsed time' :



    Start Date - This is in the usual date format of DD/MM/YY

    Start Time - This is in decimal form, rather than hours and minutes. This will therefore show, for example, 9:25 (9 hours and a quarter) for 9:15 (quarter past 9). I have an Access query which transforms this into a hexidecimal (hours and minutes) form (see below).

    Code:
     SELECT DaisyMSR.[Start Time], 
    Int([Start Time]) AS Expr2, 
    [Start Time]-[Expr2] AS Expr3, 
    60*[Expr3] AS Expr4, 
    Round([Expr4],0) AS Expr5, 
    [Expr2] & ":" & [Expr5] AS Expr6
    FROM DaisyMSR;
    Fix Date - This is in the usual date format of DD/MM/YY


    Fix Time - Same as the Start Time, this is in decimal and requires a query to convert it.

    Would I would to do, is run a query which will show me the elapsed time. I'm assuming this will have to be a combination of the two code's for 'Start Time' and 'Finish Time' but I'm no expert in programming or Access so I'm not getting very far!

    I hope it's possible!

    Thanks,

  2. #2
    dandoescode is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    85
    Code:
     SELECT ([Finish Time] - [Start Time]) AS ElapsedTime
    You'd want to make sure that time is in minutes, you can convert back after if you like.

  3. #3
    dandoescode is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    85
    Also I think theres a much more trivial way to do this using the DATEDIFF() Function.

    http://www.w3schools.com/sql/func_datediff.asp

  4. #4
    dr4ke is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    108
    Thank you, but your code won't transfer the data into a hexidecimal format and then work out the difference. However, it does seem possible if I start to build queries upon queries...

    It's something I could look into.

    Thanks

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

Similar Threads

  1. Finding the difference between the dates of two records
    By jamesborne in forum Programming
    Replies: 5
    Last Post: 01-19-2012, 06:48 PM
  2. Replies: 10
    Last Post: 12-27-2011, 01:20 PM
  3. Replies: 3
    Last Post: 07-05-2011, 02:25 PM
  4. Replies: 3
    Last Post: 08-07-2010, 06:05 PM
  5. Count difference between two dates
    By Costa in forum Forms
    Replies: 3
    Last Post: 03-09-2010, 10:38 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