Calculate the number of days elapsed since the previos date.

[MaleNurse325]

I have a report on which are some dates of activities.
The Details section reads across

Done(date field) Pump(A or B) Done By(a name)

I would like Access to calculate the number of days that have elapsed between consecutive entries.

so something like

12/10/16 A John Smith
16/10/16 B John Smith 4 Days
28/10/16 A Jane Smith 12 Days

Please help... it hurts.

:banghead:

 

[sneuberg]

Some would suggest a subquery for this as you need to get the date in another record. Allen Browne discusses that here. I think DMax is easier to use and I've found it faster than subqueries. The form of the DMax to get the date in the previous record would be something like:

DMax("[NameofDateField]", "[NameOfTable]", "[NameofDateField] <  #" & [NameofDateField] & "#")

You could plug this and the date in the current record into the DateDiff function to get the answer you want.

Note that this won't work if you have dates that are equal. If that's the case you will need to have some other field that will distinguish between the equal dates. An autonumber field would do that if you have one. That field would have to be added to the criteria of the DMax. This is also the case if you go the subquery route.

 

[MaleNurse325]

As I am very inexperienced Could you show me how I can present that in a calculated field within a report please?

The fields Are [Done], [DoneBy], & [Pump]

The Table is [tblColdWater]

 

[sneuberg]

The expression for the days elapsed would be:

Days: DateDiff('d',DMax("[Done]","[tblColdWater]","[Done] <  #" & [Done] & "#"),[tblColdWater]![Done])

to tack "Days on the end of that except where null (the first date):

DaysLabeled: IIf(IsNull([Days]),Null,[Days] & " Days")

These are demonstrated in the attached database. The test dates from your first post were entered in American format (mm/dd/yyyy) so if you want to test for these dates you might have to reenter them. The Days expression will have to be displayed in the query for the DaysLabeled to work but it can be left out of the report or if you don't mind really long expressions you can substitute the Days expression for Days in the DayLabeled expression.

Please note that this will NOT function properly if there are dates that are equal. If that the case another field will have to be add to the criteria to distinguish like dates. Let me know if this is a problem and what the field would be.

 

[CJ_London]

@sneuberg - you might find this thread of interest

https://www.experts-exchange.com/articles/1921/Access-Techniques-Fast-Table-Lookup-Functions.html

guess it really depends on size of the table and appropriate indexing as to whether performance differences are noticable

 

[sneuberg]

@sneuberg - you might find this thread of interest

Yes thanks I do find this interesting.

In one of my posts in this thread I said that I found an aggregate function to be faster than a subquery. I don't remember the details anymore but in that case the aggregate was more than ten times faster. I never looked into why. This looks like it might explain it.