Calculating two dates in a form in Access

[jalenben1]

I have a form I am creating in access. I would like to calculate the difference between two dates in the following format: dd:hh:mm:ss.

The fieldnames I have is DateCreated and Occurence-Date. For example:

Occurence-Date DateCreated
3/26/2010 4:37:00 AM 3/26/2010 4:44:58 AM

The time should be 00 0:07 (that is the format I want to use) How would I acheive that in a form. I know that I have to use a text box in the form but I am not sure how to calculate that.

 

[traingamer]

You can get the difference in seconds by using:

totalseconds = datediff("s", Occurence-Date, DateCreated)

then calculate from there:
seconds in a day = 86400
seconds in an hour 3600
etc

or

just use the example in the help file:

Function ElapsedTime(endTime As Date, startTime As Date)
    Dim strOutput As String
    Dim Interval As Date
    
    ' Calculate the time interval.
    Interval = endTime - startTime
 
    ' Format and print the time interval in seconds.
    strOutput = Int(CSng(Interval * 24 * 3600)) & " Seconds"
    Debug.Print strOutput
        
    ' Format and print the time interval in minutes and seconds.
    strOutput = Int(CSng(Interval * 24 * 60)) & ":" & Format(Interval, "ss") _
        & " Minutes:Seconds"
    Debug.Print strOutput
    
    ' Format and print the time interval in hours, minutes and seconds.
    strOutput = Int(CSng(Interval * 24)) & ":" & Format(Interval, "nn:ss") _
           & " Hours:Minutes:Seconds"
    Debug.Print strOutput
        
    ' Format and print the time interval in days, hours, minutes and seconds.
    strOutput = Int(CSng(Interval)) & " days " & Format(Interval, "hh") _
        & " Hours " & Format(Interval, "nn") & " Minutes " & _
        Format(Interval, "ss") & " Seconds"
    Debug.Print strOutput

End Function

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[jalenben1]

I am little confused. In my form under design view I have the text box and when I go to PROPERTIES and I do to control source and put in this formula totalseconds = datediff("s", Occurence-Date, DateCreated)...that is suppose to work? It did not work for me.

 

[traingamer]

Try just [blue]datediff("s", Occurence-Date, DateCreated)[/blue] in the control source.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[jalenben1]

Traingamer

I put that in the control source in the text box and it gives me #Name?. Any more suggestions?? Am I to use a text box to do this??

 

[MichaelRed]

Cccurance-Date" may be interperted as Occurance - Date. This would -at best produce results not in your apparent best interest.

Also, the 'expression' as typed appears to refer to DATA set values, rather than the control displayed values. It may be helpful to provide more specific information regarding the actual and specific details re these items and elements.



MichaelRed

 

[dhookom]

This is at least the third thread started today where users have used junk characters or spaces in the field/table/object names.

For the third time (today):
You do the crime (using spaces or junk in object names),
you do the time (entering []s around the names).

Duane
Hook'D on Access
MS Access MVP

 

[MichaelRed]

but, sadly, it appears that no one aka [bold]jalenben1[\bold] listens ...


MichaelRed