Calculate DateDiff Between 2 records in same table/query

[Drand]

Hi All

I am trying to calculate the elapsed time between 2 records in the same table. I have a table which records rainfall data per day. Obviously, not all days are recorded.
I need to know the number of days between 2 rain records so basically this is the elapsed time between the latest record and the one before that.
Is this possible?

Many thanks

 

[Drand]

Ultimately, the data will appear on a form. Would that work also?

 

[CJ_London]

It's possible - sql would be something like this using a subquery

SELECT readDate, readDate-(SELECT max(readdate) FROM myTable T WHERE readDate<myTable.readDate) as duration
FROM myTable

Reports have additional functionality for this sort of thing, for forms, you will need to use a query

 

[Drand]

I could use a report if that is easier but I am not sure what the functionality is that you refer to.

Thanks

 

[Drand]

Thanks for your reply.

Perhaps I didn't explain this properly. I am not looking to find elapsed time between the entry of records.

What I am looking to do is determine the number of days between the occurrence of each event. This is a table which records daily rainfall and I am trying to determine the longest period of drought during the 10 year period that data has been captured.

In other words, what is the maximum difference between each consecutive date in the table/query.

Thanks

 

[CJ_London]

have you tried the query in post#4? You'll need to modify for your table and field names

However this would appear to be a different requirement

In other words, what is the maximum difference between each consecutive date in the table/query.

suggest you provide some example data and the result you want from that example data.

 

[Drand]

have you tried the query in post#4? You'll need to modify for your table and field names

However this would appear to be a different requirement


suggest you provide some example data and the result you want from that example data.

Thanks for your response.

This is a graphic of what I am trying to do.

 

[Eugene-LS]

I need to know the number of days between 2 rain records so basically this is the elapsed time between the latest record and the one before that.
Is this possible?

I think it's possible ...

Function:

Public Function ElapsedTimeInDays(vDate) As Long
'   I am trying to calculate the elapsed time between 2 records in the same table.
'   I have a table which records rainfall data per day. _
       Obviously, not all days are recorded.
'----------------------------------------------------------------------------------------------
Const DatesTableName$ = "Query1"  'Table or query name
'Const DatesTableName$ = "dtDates" 'Table or query name
Const DateFieldName$ = "dtdDate"  'Date field name in table - Beter if it's indexed
Dim sVal As String, vPrevDate
On Error GoTo ElapsedTimeInDays_Err

    If IsDate(vDate) = False Then Exit Function
    sVal = DateFieldName & " < " & Format$(vDate, "\#mm\/dd\/yyyy\#")
    vPrevDate = DMax(DateFieldName, DatesTableName, sVal)
    ElapsedTimeInDays = DateDiff("D", Nz(vPrevDate, vDate), vDate)

ElapsedTimeInDays_Err:
    Err.Clear
End Function

"Qurery1" (Just for example):

SELECT dtdDate,
    ElapsedTimeInDays([dtdDate]) AS ElapsedDays
FROM dtDates
WHERE (
    (dtdDate>#8/9/2021#)
    AND
    (dtdDayNo) In (1,4,6));

Result:

 

[CJ_London]

Thanks for your response.

yes - but did you try the sql in post #4.

 

[Eugene-LS]

but did you try the sql in post #4.

I did

I think we should not minus one date value from another because there may also be TIME
Please try :

Dim dBefore As Date, dAfter As Date
    
    dBefore = #3/2/2022 11:56:00 PM#
    dAfter = #3/3/2022 1:56:00 AM#
    
    Debug.Print "Minus in days:"; Format(dAfter - dBefore, "0.000")
    Debug.Print "DateDiff in days:"; DateDiff("d", dBefore, dAfter)

Returns:

Minus in days:0,083
DateDiff in days: 1

 

[arnelgp]

see also this demo using Plain query.

qryPreviousDate will get the previous date.
qryDifference is the final query that will show the Date difference and rainfaill difference of the two dates.

 

[CJ_London]

I think we should not minus one date value from another because there may also be TIME

OP has not made any mention of time. But if so I would use the datevalue function

 

[Eugene-LS]

qryDifference is the final query that will show the Date difference and rainfaill difference of the two dates.

A very good solution, but very difficult (for computer).
I checked your "qryDifference" query on 10,000 records.
On my machine (not weak), it was executed for more than 10 minutes, and then I stopped it (tired of waiting).

If rewrite query "qryPreviousDate" :

SELECT data.*,
    DateValue(Nz(DMax("recordingDate","data",
    "[recordingDate] < " & Format([recordingDate],"\#mm\/dd\/yyyy\#")),[recordingDate])) AS previousDate
FROM data
ORDER BY recordingDate;

- query "qryDifference" will execute much faster (less than a minute)

 

[arnelgp]

using temp table and a form (datasheet form, dsForm) can speed things up.

 

[Eugene-LS]

using temp table and a form (datasheet form, dsForm) can speed things up.

This is a good idea!
...
I allowed myself to optimize the code and query a little more
Below all the same as above (post #17)- but noticeably quicker:

 

[arnelgp]

you don't even need to add rainfall02, since Query1 is still being used.
also no need for the subID. what is slow is my choice of adding record
using select statement.

 

[Eugene-LS]

you don't even need to add rainfall02, since Query1 is still being used.

No, in "rainfall_v02.accdb" is used "Query2" ...

 

[arnelgp]

see the code of the button.

 

[Eugene-LS]

see the code of the button.

Excuse me, - which button?
...
I see your changes. It is acceptable and so, of course, but the relation by Long fields (as in my version) works faster than by Date fields (Double)

 

[arnelgp]

The open event.