DateDiff Output Formating

I know there are loads and loads of answered questions concering this topic, but either they don't exactly fit what I was looking for, or I simply don't understand the answers enough to implement them in my own project.  I'm an Access 2007 Newb, and VBA programming is, so far, well beyond me, so please be patient with  your answer, I'll need as much step-by-step detail as possible, and i will try to provide enough information for you to work with.

Okay, so, this is what I'm working with:

I was tasked by my wife to create an Access Database for tracking work calls that she takes.  This DB is going to be for tracking length of call, per minute rate for the call, and money earned on the call AFTER the per minute overhead is deducted.  Not really having much of an idea as to where to begin, I downloaded the template on called "Call Tracker", thinking that this might already be exactly what I was looking for.  Needless to say, it wasn't, so I began the process of MAKING it into what I needed it to be.  So I've made a bunch of changes, and now have the following question:

I have a table called "Calls" and a form called "Call Details"  (I realize these don't really meet standard naming conventions, but I didn't mess with the names of anything, I just left them the way they were in the template for fear of totally messing things up)

On the form "Call Details", I have:

Text Box One:  [Start Time] with button, btnStart,  which, when pressed enters current time in [Start Time]
Text Box Two: [End Time] with button, btnEnd, which, when pressed enters current time in [EndTime]
Text Box Five:  [PerMinute], used for the user to enter the per minute rate for that particular call

Text Box Four  [TotalTime], which displays the results of a calculation done by pressing a button, btnTotal

The Following code is on btnTotal, which is pressed in order to fully calculate the length of time for that particular call.  I used:

 [TotalTime] = (DateDiff("s", [StartTime], [EndTime]))

So this will only output the difference in time in Seconds, which I can use by diving by 60 and then multiplying by the per minute rate, ect, for my other needs.  I would have gone with minutes, but her calls are tracked by the second, so easier to go with seconds for the other calculations, but what I'd like is for the user to not have to see a bunch of seconds, but to be able to see something such as 5:25 instead of 325.  I've tried multiple things but still can't figure out what I need to do simply to make this display properly and still be useable for other calculations which need to be made based off of this number.
Who is Participating?
GrahamMandenoConnect With a Mentor Commented:
You are very much on the right track - congratulations!   :-)

However, you should note that [TotalTime] should not be a field in your table.  As a general rule, you should never store values in a table that are dependent on and can be easily calculated from other fields.

I suggest you base your form on a query of your table, not on the table itself.  To this query, add all the fields from the table and also add two calculated fields thus:
TotalTime: DateDiff("s", [StartTime], [EndTime])
TotalCost: Round( [TotalTime] / 60 * [PerMinute], 2 )

This query will then display the duration of the call in seconds, and also the total cost.  These values will change automatically as the three source values change, and no recalculation is required.

In your form, you can then create a textbox with this expression as its ControlSource:
=[TotalTime] \ 60 & Format( [TotalTime] Mod 60, ":00" )
This will display the time in mm:ss format.

You can also add a textbox bound to the other calculated field - TotalCost - formatted as Currency.

Best wishes,
Graham Mandeno [Access MVP 1996 - 2012]
JARichardAuthor Commented:

I tried your solution, but now have a problem in that now, upon opening the form, I am recieving pop up boxes to enter Values for StartTime, EndTime, and PerMinute before the form will even open.  The purpose of the form is to enter these items, but with the query I just created it is asking for this informatioin before the form is even loaded so that it can populated the Total Time and Total Cost Text Boxes I created per your suggestion.
JARichardAuthor Commented:
Oh, and just to try out the rest of it, I entered in values for those items, and the form would open, but the TotalTime Text Box still does not actually display calculated Seconds, only minutes.  I had entered
12:00:00 AM and 12:01:54 AM as times, in which case the difference would be 1:54  All I got, however was the 1 and no 54.  Then, looking at the code you suggested I put in the text box for this:

 =[TotalTime]\60 & Format([TotalTime] Mod 60,":""00""")

After a bit of "Googling", I found the following code which worked for this aspect of things:

=([TotalTime]\60) & ":" & ([TotalTime] Mod 60)

And I fixed the issue with the opening of the form.  I had used incorrect Values in the code you had provided.  EndTime and StartTime were the names of the text boxes displaying the information on the form, but not the name of the actual fields in the table.  once I changed those to mirror the table field names, all worked well.  So, that said, This question is closed out.  Thank You very much for you assistance.  I am going to award you full points even though I had to google a bit to find the actual way for the formatting to show up because I never would even have known where to start if it weren't for the answer that you DID provide, and your suggestion of the the query instead of the table entries was invaluable, and I learned enough doing it, that it negates a couple of the other things I had on my list of questions to ask on here as now I can do them myself.

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

JARichardAuthor Commented:
Easy to follow, gave me a definately place to start in searching further to find what would work exactly as I needed, and also provided some bricks which I will be able to use to assist in building the rest of my project.
Dale FyeCommented:
You might also want to consider a function for this, which would accept two values (StartTime and EndTime), you could then use:

=ElapsedTime(me.txt_StartTime, me.txt_EndTime)


=ElapsedTime([Start_Time], [End_Time])

This is a function I have used on several occassions.
Public Function ElapsedTime(Optional StartTime As Variant, Optional EndTime As Variant) As String

    Dim Sec As Long
    'If either the start or end time is missing then exit
    ElapsedTime = "N/A"
    If IsMissing(StartTime) Or IsMissing(EndTime) Then
        Exit Function
    ElseIf IsNumeric(StartTime) = False Then
        Exit Function
    ElseIf IsNumeric(EndTime) = False Then
        Exit Function
    End If
    Sec = DateDiff("s", StartTime, EndTime)
    If Int(Sec / 3600) > 0 Then
        ElapsedTime = Int(Sec / 3600) & ":"
        Sec = Sec Mod 3600
    End If
    ElapsedTime = ElapsedTime & Format(Int(Sec / 60), "00") & ":"
    Sec = Sec Mod 60
    ElapsedTime = ElapsedTime & Format(Sec, "00")
End Function

Open in new window

Hi JARichard

I'm sorry about the confusion between field names and textbox names, and I'm glad you got that sorted.

There is still a small problem with the expression you are using to display the time.  If the "ss" value is less than 10 then it will not display a leading zero - for example, 125 seconds will display as 2:5, not 2:05.  That was the purpose of the Format function in the expression.

My expression was not working because I omitted a backslash before the colon in the Format string.  Just a typo - sorry about that!  It should be:
=[TotalTime] \ 60 & Format( [TotalTime] Mod 60, "\:00" )

Best wishes,
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.