Concatenating Date and Time Fields for Lag Time Analyses

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table in an Access database which has a couple of date fields (in date format) and a couple of time fields (in time format). I am trying to calculate the elapsed minutes between the first date/time combination and the second date/tiem combination but am having difficulty doing the lag utilizing both the date and time fields. Any idea on how I might approach this?

Thanks.
 
You can simply add the date and time fields together and compare the results.

SQL = UPDATE zTestTbl SET zTestTbl.ElapsedTime = DateDiff("n",([date1]+[time1]),([date2]+[time2]));

Both [date1] and [date2] are defined as Short Date fields.
Both [time1] and [time2] are defined as short Time fields.
[ElapsedTime] is defined as a Long Integer field.

The "DateDiff" function, with "n" as the first argument returns the difference in minutes of ([date2]+[time2]) - ([date1]+[time1]).
If you want to return the difference in seconds use "s" in place of "n".
See the help for the function "DateDiff" to get more details.
 
Back
Top