Time Tracking

[ladybug via AccessMonster.com]

I have a query with two tables

Table 1: tblDetail
Table 2: tblAdvEvent

Table 1 has "Date" which is actually a date and time. Example: 11/12/2007 10:
02:16 AM
Table 2 has "Faxed On" which is also a date and time.

I need in a query to be able to tell how many days, hours, minutes, seconds
it took from "Date" to "Faxed On." Can someone help me create a new field in
my query that tells me the time between the two?

 

[Michel Walsh]

From what I understand (and I can be very wrong), something like:


SELECT table1.event, MIN(table2.DateTimeField - table1.DateTimeField)
FROM table1 INNER JOIN table2 ON table2.DateTimeField > table1.DateTimeField
GROUP BY table1.event



Vanderghast, Access MVP

 

[Dale Fye]

Ladybug,

Access has a function DateDiff( ) which accepts an increment, and two
date/time values, and determines the number of 'increments' between the two
values. Unfortunately, the result of DateDiff("d", #9/15/08 23:59#, #9/16/08
00:01) is 1 day.

The good news is that date/time values are actually stored as double
precision numbers where the integer value indicates the number of days since
12/30/1899 and the decimal portion represents the fraction of a 24 hour day
(so .5 = 12 hours).
I actually have a function that I use for situations like this.

Public Function fnElapsedTime(DT1 As Variant, DT2 As Variant) As Variant

Dim dblElapsed As Double

'The date time values are dimensioned as variants to all for nulls
If IsNull(DT1) Or IsNull(DT2) Then
fnElapsedTime = Null
Else
dblElapsed = Abs(DT2 - DT1)
fnElapsedTime = Int(dblElapsed) _
& Switch(dblElapsed >= 2#, " days ", _
dblElapsed >= 1#, " day ", _
True, Null)
dblElapsed = dblElapsed - Int(dblElapsed)
fnElapsedTime = fnElapsedTime & Format(dblElapsed, "h:nn:ss")
End If

End Function

If you actually want it formatted like: 3 days, 22 hours, 15 minutes
Then you will have to play with dblElapsed (*24) to get the number of hours,
then strip the integer portion of that, and multiply by 60 to get the number
of minutes.
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Lord Kelvan]

problem with michels query is it dosnt join the two tables there is no
way to note which date time in table2 is the one that belongs to
table1

what is the table structure for table1 and table2 because i am hoping
you have an id field linking the two tables.

SELECT table1.event, format(table2.DateTimeField -
table1.DateTimeField,"dd hh:nn:ss")
FROM table1 INNER JOIN table2 ON table2.idfield = table1.idfield

and i may be wrong with the above witht he format thing to display
dats hours minutes and seconds

hope this helps

Regards
Kelvan

 

[Michel Walsh]

Each record in table1 is associated with the UNIQUE date_time value in
table2 which is immediately AFTER the date time in table1. I don't see any
problem with that.

Vanderghast, Access MVP