Page 1 of 3 123 LastLast
Results 1 to 15 of 43
  1. #1
    bjnorth is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2013
    Posts
    19

    How do I calculate in Military time when the stop time is smaller than the start time

    Hello, I am an Access newbie and somtimes feel like a dummy. So, here goes. I have created a table with time Fields: Call Received & Call Cleared. I have instructed my users to use military time. I am trying to calculate how long each call lasted. I created a query and added a calculated field using this statement:


    Code:
     Minutes: DateDiff("n",[CallReceived],[CallCleared])
    It works great except when my Call Cleared field is less than my Call Received field. For example: Call Received: 20:05 Call Cleared: 00:00 (midnight). The number I get is -1205. Another example: Call Received: 22:18 Call Cleared: 1:28 I get the number -1250

    I'm was never a math whiz but I did pass (many moons ago). I know there is a solution...ha...I just don't know what it is.

    Can any of you smart people out there please tell me how to fix it?

    Thanks so much !!!


  2. #2
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Posted in error - sorry.

  3. #3
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Since I trampled all over your post I feel duty bound to respond. I assume your users enter a time and only a time. OK, if the result is negative then simply add 24 * 60. Here's the sort of code I would expect.

    Minutes + DateDiff("n", [CallReceived], [CallCleared])
    If Minutes < 0 then Minutes = (24 * 60) + Minutes

  4. #4
    bjnorth is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2013
    Posts
    19
    Hi Rod,

    Thanks so much for your reply. I entered the code as you suggested and I received the following message:

    The expression you entered contains invalid syntax.
    You may have entered an operand without an operator.

    Thanks,

    BJ

  5. #5
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Where are you entering this code?

    PS Sorry. There's a typo in the first line, it should be:

    Minutes = DateDiff("n", [CallReceived], [CallCleared])

  6. #6
    bjnorth is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2013
    Posts
    19
    Hi Rod,

    I am putting the code in the Field section in the bottom screen of the Query Design window. Perhaps I should be putting it somewhere else? As I said....I am an Access Newbie.

    Here's what I am putting in that Field section:

    Minutes: DateDiff("n", [CallReceived], [CallCleared])If Minutes < 0 then Minutes = (24 * 60) + Minutes

    I get the error: The expression you entered contains invalid syntax.
    You may have entered an operand without an operator.

    When I put this code in:

    Minutes = DateDiff("n", [CallReceived], [CallCleared])
    If Minutes < 0 then Minutes = (24 * 60) + Minutes

    I get the error: The expression you entered contains invalid syntax.
    You may have entered an operand without an operator.

    When I put this code in by itself:
    Minutes: DateDiff("n", [CallReceived], [CallCleared])

    I do get mostly correct minutes except for when the CallCleared time is less than the CallReceived time. Then I get a negative #. (This is the problem)

    When I put this code in by itself:
    Minutes=DateDiff("n", [CallReceived], [CallCleared])

    This is what shows up in the field:

    Expr1: [Minutes]=DateDiff("n",[CallReceived],[CallCleared])
    and when I hit !run it tells me to Enter parameter value for Minutes

    So, as you can see I am obviously not doing something right. I can't tell you how much I appreciate your help on this.

    Thank you! Thank you! Thank you!

    BJ

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    In the query, in a separate column on the grid, try

    Minutes: DateDiff("n", [CallReceived], [CallCleared])

    Let us know what happens.

  8. #8
    bjnorth is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2013
    Posts
    19
    Hello,

    What I am trying to accomplish here is to determine how much time is spent per call. The following code works for the most part but when the CallCleared field is less than the CallReceived field it gives me a negative number. For example: Call Received: 20:05 Call Cleared: 00:00 (midnight). The number I get is -1205. I need it be be 235 minutes. Another example: Call Received: 22:18 Call Cleared: 1:28 I get the number -1250. I need to to be 190 minutes.


    When I put this code in:
    Minutes: DateDiff("n", [CallReceived], [CallCleared])

    I do get mostly correct minutes except for when the CallCleared time is less than the CallReceived time. Then I get a negative #. (This is the problem)


    Thanks so much for the help !!!

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Is the value correct , only negative?

    If so, try this:

    Minutes: Abs(DateDiff("n", [CallReceived], [CallCleared]))

  10. #10
    bjnorth is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2013
    Posts
    19
    No, the values are not correct. For example: Call Received: 20:05 Call Cleared: 00:00 (midnight). The number I get is -1205. I need it be be 235 minutes. Another example: Call Received: 22:18 Call Cleared: 1:28 I get the number -1250. I need to to be 190 minutes.

    Thanks,

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    How are these fields defined? What data type?
    Perhaps if the Date was included in the calculated properly.

    Seems you are dealing only with Time and excluding Date.

  12. #12
    TOMMY.MYERS668's Avatar
    TOMMY.MYERS668 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Feb 2013
    Location
    West Virginia
    Posts
    77

    Re: How do I calculate in Military time difference

    I dont know the exact syntax for datediff as I have only used it once in my life. But as long as u know that the following may help.


    Dim ttm as date 'time to midnight
    Dim tam as date 'time after midnight

    If [start time] &lt; [end time] then
    me![total time]=Dattediff calc normaly
    Else
    Ttm=datediff [start time] to 2359
    Tam=datediff 0000 to [end time]
    Me![total time]=ttm+tam+1
    End if

    You could put this into the after update event on end time and it will calc as soon as you enter the end time.
    Im on my phone so of course it doesnt let meput in a less than sign it goes between the start and end time in the if statement instead of &amp; lt ;

  13. #13
    TOMMY.MYERS668's Avatar
    TOMMY.MYERS668 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Feb 2013
    Location
    West Virginia
    Posts
    77

    Re: How do I calculate in Military time difference

    Hey orage u seem to know the exact code could u help to make more sense of what I posted

  14. #14
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you tell us how the fields are defined? What is the data type? Since you are dealing with times that could start on Thursday night, and end just after midnight (which is now Friday) you should be including the Date as well as the time in your calculations.

    You can find out the syntax of most Access functions and examples at
    http://www.techonthenet.com/access/functions/

  15. #15
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here's a small routine to show what I'm talking about. If you define your Times such that the Date is included.
    You will not have the issues you have currently.

    When you record the CallStart you can use Now to assign the date and Time to CallStart. And when you record CallCleared you use Now to assign the date and Time to CallCleared.

    Code:
    Sub MilitaryTime()
    
    Dim CallStart As Date               'General Date format
    Dim CallClearedAt As Date        'General Date format
    'Simulate a time today and a time just after midnight
    CallStart = #2/21/2013 11:50:00 PM#   '
    CallClearedAt = #2/22/2013 12:03:25 AM#
    '
    'Get the difference in seconds, then divide by 60 to have result  in minutes.
    Debug.Print DateDiff("s", CallStart, CallClearedAt) / 60 & "  minutes"
    End Sub
    Good luck.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Import Wizard does not start from time to time
    By emmett in forum Import/Export Data
    Replies: 1
    Last Post: 04-06-2012, 07:16 AM
  2. Calculate start time.
    By Mantaii in forum Reports
    Replies: 5
    Last Post: 02-28-2012, 12:31 PM
  3. Replies: 7
    Last Post: 08-04-2011, 07:49 PM
  4. Military Time
    By tshirttom in forum Access
    Replies: 3
    Last Post: 07-29-2011, 01:29 PM
  5. auto start/stop time
    By j0ker21m in forum Reports
    Replies: 1
    Last Post: 12-10-2005, 08:42 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