Calculate DateDiff Between 2 records in same table/query (1 Viewer)

[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

 

[Pat Hartman]

Doing this in a query is very inefficient. Doing it in a report is more efficient. Will that suffice?

 

[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

 

[Pat Hartman]

If you bind a form to a query that includes aggregate functions such as Max(), that query will not be updateable. This information seems more appropriate to a report than a form. Forms are dynamic and can be sorted. Sorting requires recalculating the elapsed time from the "previous" record.

Since you are recording history, you would have no need to update prior records. You can specifically lock all but the most recent record to prevent old records from being updated. If you do that, it is safe to calculate the elapsed time as you add a new record. Keep in mind however, you ABSOLUTELY MUST enter the data in order. You can not enter a record that falls in between two other records since that would mess up the calculated value. You would also need code to prevent entering data earlier than the newest record. All of this explains why storing calculated values violates normal forms and we strongly discourage it. In this case, I'm guessing that you will want graphs in addition to reports and those will be based on queries and that will put you back to calculating on the fly. To split the baby, don't show the calculated value on the form. Use a separate query to create the graph than is used to do data entry.

Think about the report. WHY does someone doing data entry have to see the elapsed time?

 

[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.

 

[Pat Hartman]

I am not looking to find elapsed time between the entry of records.

And I was not talking about elapsed time between the entry of records. I was talking about the elapsed time between the dates recorded in those records. Since it can rain multiple times in one day, assumed you were recording the date AND the time. If you only ever have one rain record in a day, then all you are dealing with is days but that doesn't change what I said.

 

[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" ...