Date & Time Stamp Formula (1 Viewer)

[HeelNGville]

Good evening!

With all the knowledge contained by individuals monitoring this site, I am confident that someone can offer advice to my issue. Basically, I have a table that contains productivty performance by user id. The table also houses date & time stamp (hh:mm:ss) data as it relates to each transaction . I would like to write a formula that calculates "gap time" from one transaction to the next. Ex.

ID Infor Units Date Time Gap Time
USERID123 5 4/13 05:44:33
USERID123 2 4/13 05:44:58 (Result would be 00:00:25)
USERID123 1 4/13 05:46:59 (Result would be 00:02:01)

I can export this file out to Excel, write the formula and import back into Access, however I think that the process would be more efficient if performed in Access.

Thanking anyone in advance for your assistance!

Regards,

Rick

 

[rich.barry]

I don't think you can call a value of a previous record to use in a query calculation, but you could do it by writing a custom function.

Make Date & Time a single field so that it is a numeric value we can perform calculations on, then make a query with DateTime as a field sorted Ascending and GapTimeiff(DateTime) as another field.
Put a custom function in a VB module...

Function Diff(DateVal as Single) as Single
Static LastDate as Single
Diff=DateVal-LastDate
LastDate=DateVal
End Function

Format the GapTime field as dd hh:mm:ss

Richard

 

[HeelNGville]

I don't think you can call a value of a previous record to use in a query calculation, but you could do it by writing a custom function.

Make Date & Time a single field so that it is a numeric value we can perform calculations on, then make a query with DateTime as a field sorted Ascending and GapTimeiff(DateTime) as another field.
Put a custom function in a VB module...

Function Diff(DateVal as Single) as Single
Static LastDate as Single
Diff=DateVal-LastDate
LastDate=DateVal
End Function

Format the GapTime field as dd hh:mm:ss

Richard

Thanks for the help Rich!
I continued to toy with this upon posting the initial thread and I was able to obtain the solution I was looking for by utilizing the following within a query:

FieldX
TimefromPreviousRow: IIf(IsNull(DMax("[Date Time]","tblEmplProduction"," [Date Time]<#" & [Date Time] & "#")),Null,CDate(DMax("[Date Time]","tblEmplProduction"," [Date Time]<#" & [Date Time] & "#")))

FieldY
TimeElapsed: Format([Date Time]-[TimefromPreviousRow],"h"" hr ""n"" min ""s"" sec""")