I have two text boxes on my form and I would like to know how to calculate my date and time by adding another text box.



DMIStartDate&Time DMIEndDate&Time
4/8/2009 8:20:36 AM 4/8/2009 8:26:02 AM
5/7/2009 4:42:00 PM 5/7/2009 5:09:00 PM


New text box = Elasped Time ??????


big0

You'll want to use the datediff() function in your elapsed time textbox control. How do you want the elapsed time formatted?

For example, the following would give you the elapse time in seconds

=DateDiff("s",[StartDateTime],[endDateTime])

Edited by: jzwp11 on Fri Sep 25 12:57:24 EDT 2009.

Thanks for your response.

I figured part of it and I using this code.

=Format([DMIStartDate&Time]-[DMIEndDate&Time],"hh:mm:ss")


I would like the results to show: ex 1 hr 5 min and 26 seconds.


any though on how i can do this/


big0

=DateDiff("s",[StartDateTime],[endDateTime])

Once you do the above DateDiff to get the seconds, you then have to perform a series of integer divides to convert Hours, Minutes, Seconds.

To convert Seconds to Hours, you divide by 3600, but you want an Integer divide, so as to get a whole number of hours:

Hours = Seconds \ 3600

Then you need to get the remainder of seconds after getting the hours:

Seconds = Seconds Mod 3600

To get the minutes, you integer divide by 60

Minutes = Seconds \ 60

To get the seconds another Mod operation

Seconds = Seconds Mod 60

Then put the results together:

=Hours & ":" & Minutes & ":" & Seconds

Or whatever other way you want to do it.

As jzwp11said, you'll need to use DateDiff() for this. Create a standard (stand alone) module and place this code in it:


When asked to name the module, name it DateHandler.

Now, where ever you want to do this, call the function like this

Do I add this code to the After Update event field?


If Not IsNull(Me.DMIStartDate&Time) And Not IsNull(Me.DMIEndDate&Time) Then Me.NewTextBoxName = ElapsedTime(Me.DMIStartDate&Time, Me.DMIEndDate&Time)End If



bigO

I believe you should put the code in the on current event of the form that way it will calculate the elapsed time for each record in the form's recordset.


Alternatively, you could use the following immediate IIF() function in the control source of your textbox & call the function from there:

=IIf(Not IsNull([startDateTime]) And Not IsNull([enddatetime]),elapsedtime([startdatetime],[enddatetime]),"cannot calculate")

This is cool. I like this way.

I tried using your code in the control source, but keep getting an error. The text box fills in as "#Name?"



My Code
=IIf(Not IsNull([DMIStartDate&Time]) And Not IsNull([DMIEndDate&Time]),GetElapsedTime([DMIStartDate&time],[DMIEndDate&Time]),"cannot calculate")


Here is my function:



'---------------------------------------------------------------------------------------
' Module : fnGetElapsedTime
' DateTime : 12/29/2006
' Author : Noah Pruitt
' Purpose : Calculate Days/Hours/Minutes/Seconds between Date() fields.
' Help Text : John White,
'---------------------------------------------------------------------------------------

Option Compare Database
Option Explicit

Public Function GetElapsedTime(interval)
'
'---------------------------------------------------------------------------------------
' Procedure : GetElapsedTime
' DateTime : 12/29/2006
' Author : Noah Pruitt
' Purpose : Calculate Days/Hours/Minutes/Seconds between Date() fields.
'
' Use : The interval argument is flexible; it can be an expression or a field
' reference:
'
' Literal values for long dates (run this in VBA Immediate Window):
'
' ? GetElapsedTime(#6/3/93 1:00PM# - #6/1/93 8:00AM#)
'
' In a query: ElapsedTime: GetElapsedTime ([endtime] - [starttime])
' See qryTimeBetweenDates in this database.
'
' In VBA Code: txtElapsedTime = GetElapsedTime (txtendtime - txtstarttime)
' Note: control names prefaced with "txt" to denote textbox controls.
'
' Logic : IF Days <= 0, do not add the "Day"/"Days" segment to the result.
' IF HH/MM/SS = 0, do not add the segment to the result.
' IF HH/MM/SS > 0 AND HH/MM/SS > 1, then add an 's' to the segment.
' IF HH/MM/SS > 0 AND HH/MM/SS = 1, then do not add 's' to the segment.
'---------------------------------------------------------------------------------------
'
Dim totalhours As Long, totalminutes As Long, totalseconds As Long
Dim days As Long, hours As Long, Minutes As Long, Seconds As Long
Dim strET As String

On Error GoTo ErrorHandler



days = Int(CSng(interval))
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
totalseconds = Int(CSng(interval * 86400))
hours = totalhours Mod 24
Minutes = totalminutes Mod 60
Seconds = totalseconds Mod 60

If days > 0 Then
strET = days & " Day"
If days > 1 Then
strET = strET & "s"
End If
strET = strET & " "
End If

If hours > 0 Then
strET = strET & hours & " Hour"
If hours > 1 Then
strET = strET & "s"
End If
strET = strET & " "
End If

If Minutes > 0 Then
strET = strET & Minutes & " Minute"
If Minutes > 1 Then
strET = strET & "s"
End If
strET = strET & " "
End If

If Seconds > 0 Then
strET = strET & Seconds & " Second"
If Seconds > 1 Then
strET = strET & "s"
End If
strET = strET & " "
End If

GetElapsedTime = strET


ErrorHandler:





End Function

Where did you get this code? It's not from any responses here.

The code you're using to call the function is

GetElapsedTime([DMIStartDate&time],[DMIEndDate&Time])

but the function definition is

Public Function GetElapsedTime(interval)

You're passing two date/time values to the function, and all the function asks for is a single argument, Interval!

Also, you appear, once again, to be trying to simply subtract one date/time value from another, and you cannot do this. If it was as simple as that, there would be no need for the DateDiff() function.

Either use the code I've given you or Frank's code, they'll both do the job.

The function you provided will return a blank if either date is missing, so you would not need the IIF() function I provided unless you want to show "cannot calculate".


But to get the function to work properly the ending date/time must be listed first and the two have to be separated by a hyphen not a comma per the instructions included in the code

So your expression would look like this:


=GetElapsedTime([DMIEndDate&Time]-[DMIStartDate&time])

If you still want to return the "cannot calculate" value, then this should work

=IIf(Not IsNull([DMIStartDate&Time]) And Not IsNull([DMIEndDate&Time]),GetElapsedTime([DMIEndDate&Time]-[DMIStartDate&time]),"cannot calculate")

...but you can simplify the above further by checking whether the result of the function is Null directly:

=IIf(IsNull(GetElapsedTime([DMIEndDate&Time]-[DMIStartDate&time])),"cannot calculate",GetElapsedTime([DMIEndDate&Time]-[DMIStartDate&time]))

Edited by: jzwp11 on Fri Sep 25 16:41:22 EDT 2009.

Thanks for responding missinglinq - Utter Access Veteran


I'm trying to use this code, that jzwp11 - Utter Access VIP gave me.

=IIf(Not IsNull([DMIStartDate&Time]) And Not IsNull([DMIEndDate&Time]),GetElapsedTime([DMIStartDate&time],[DMIEndDate&Time]),"cannot calculate")

My function is GetElapsedTime in lieu of GetElapsedTime(interval).

If i can call my function from a text box, would that not populate my results that I'm looking for?


big0

oK -- Let me chew on this for a little while. Again thanks for responding.

big0

This works perfectly.. Thanks jzwp11 - Utter Access VIP


big0

Glad you got it worked out. Either missinglinq's or Noah's function will work; as in the case in Access, you can get to the same solution using different routes. Good luck with your project.