Problem with series of dates 1

[embry]

Access newbie here. I've got a query that pulls a series of dates. I need to calculate the average interval between the dates. Any help would be greatly appreciated.

 

[formerTexan]

Hi Embry,

The following will give you an average in days. You will need to substitute your own SQL string and relevant variable names.
This also assumes that your dates" are actually in date formats. And you need to make a decision as to whether the recordset is going to return the dates in any sorted order. That will profoundly affect the results.

And a last caveat: the code was just written on the fly and is untested.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim date1 As Date
Dim date2 As Date
Dim lngCt As Long
Dim lngTotal As Long
Set db = CurrentDb
strSQL = "SELECT tblDates.DateID, tblDates.EventDate FROM tblDates WHERE (((tblDates.EventDate) Is Not Null))ORDER BY tblDates.EventDate;"
Set rs = db.OpenRecordset(strSQL)
rs.MoveLast
rs.MoveFirst

If Not rs.BOF And Not rs.EOF Then
date1 = rs!EventDate
rs.MoveNext
While Not rs.EOF
date2 = rs!EventDate
lngTotal = lngTotal + (DateDiff("d", date1, date2))
lngCt = lngCt + 1
date1 = date2
rs.MoveNext
Wend

Cheers,
Bill

 

[embry]

Thanks, formerTexan. I'll try to adapt that to my needs.

 

[dhookom]

Isn't the average interval the same as the total difference divided by the number of records?

(Max([YourDate])-Min([YourDate]))/Count([YourDate])

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[formerTexan]

Duh, yes. Or at least it used to when I took Intro to Statistics back in the dark ages.

Cheers,
Bill

 

[embry]

Duane, I wish that simple solution were true, but it does not seem to be. My sample dates: 5/12/04, 1/28/04, 9/28/03.

Using your method, (5/12/04 - 9/28/03)/3 = 75.67.

Actual results: 5/12/04 - 1/28/04 = 105
1/28/04 - 9/28/03 =122
Avg(105,122) = 113.5

I'm still working on this if anyone has any ideas. Thanks.
-embry

 

[Golom]

No. There are three readings but only two intervals and

datediff("d",#9/28/03#,#5/12/04#)/2 = 113.5

Duane is correct (as usual).

 

[embry]

Thanks Duane and Golom. -e