Number of days between events

[TERRY KIRKPATRICK]

Anyone have code or SQL query information on how to calculate the days from
one event to the next? Each event is a record that contains an "event date"
and I want to calculate the number of days from one event (record number 1)
to the next event (record number 2) The expression DateDiff doesn't work
since the two dates are the same field in each record.
Any help would be appreciated
Thanks
T

 

[Victor Delgadillo]

One way of doing it would be to save the first value in a global variable
using a Function. That way you can compare the difference.
This is done easier in code, where you could save the value from one reading
to the next:

(Open database in rst as recordset)
Create a loop and save the value of the day in a variable:
Dim PreviousWeek as Date

Do While Not rst.EOF
If isnull(PreviousWeek) Then PreviousWeek = rst!WeekDate ' give a
value first time
ThisWeek = rst!WeekDate
Difference = Datediff("d", ThisWeek, PreviousWeek) ' first line
will be zero
... (other fields)
PreviousWeek = rst!WeekDate
.MoveNext
Loop

Hope this helps...
--
Victor Delgadillo [MVP Access]
Miami, Florida

Consultas al grupo, asi todos nos beneficiamos.

_

 

[TERRY KIRKPATRICK]

Thanks much, I'll give this a try !

One way of doing it would be to save the first value in a global variable
using a Function. That way you can compare the difference.
This is done easier in code, where you could save the value from one reading
to the next:

(Open database in rst as recordset)
Create a loop and save the value of the day in a variable:
Dim PreviousWeek as Date

Do While Not rst.EOF
If isnull(PreviousWeek) Then PreviousWeek = rst!WeekDate ' give a
value first time
ThisWeek = rst!WeekDate
Difference = Datediff("d", ThisWeek, PreviousWeek) ' first line
will be zero
... (other fields)
PreviousWeek = rst!WeekDate
.MoveNext
Loop

Hope this helps...
--
Victor Delgadillo [MVP Access]
Miami, Florida

Consultas al grupo, asi todos nos beneficiamos.

_

Anyone have code or SQL query information on how to calculate the days from
one event to the next? Each event is a record that contains an "event date"
and I want to calculate the number of days from one event (record number 1)
to the next event (record number 2) The expression DateDiff doesn't work
since the two dates are the same field in each record.
Any help would be appreciated
Thanks
T