Average Date and Time from a table[resolved]

[ustenmacduff]

Im having some problems with a Access Program Im writing. It consists of 4 fields in a table [ID][LogDate][logintime][logouttime]

This program is for a login system for a computer lab. My problem is that I need to take the average minutes people are logged in, and use that as a running average. I then need to take than running average and use it to fill in any blank logout time fields at the end of the day.

My main question is how to I take datedifference[logintime][logouttime] and capture it into a variable for later use.

THanks.

[si_the_geek]

'replace a and b with your dates:
myVar = DateDiff("n", a, b)

"n" means minutes, it may be better to use "s" (seconds), and round it as you see fit.


I don't really agree with the idea of setting logouttime to an averaged value, but I can't think of a better alternative if you need to have a value (I would personally leave it null, as it is more accurate, and you can see who doesnt log out properly ).

[ustenmacduff]

I finally got it to work.

Heres what I did:

Dim rs As DAO.Recordset 'Recordset for query
Dim strSQL As String 'Holds SQL
Dim avgMin As Integer 'Hold Minutes
strSQL = "SELECT AVG(DateDiff('n',[TimeIn],[TimeOut])) AS AverageMinutes FROM StudentLog WHERE TimeIn Is Not Null And TimeOut Is Not Null"
Set rs = CurrentDb.OpenRecordset(strSQL)
avgMin = rs.Fields("[AverageMinutes]")
 
strSQL = "UPDATE StudentLog SET TimeOut = (DATEADD('n'," & avgMin & ",[TimeIn]))  WHERE TimeOut Is Null"
DoCmd.RunSQL (strSQL)