Calculate Days Between Records

[JimWY]

I want to know the number of days between records in a Microsoft Access database. How do I do that?
Jim

 

[Jon]

Welcome to Access World! We're so happy to have you join us as a member of our community. As the most active Microsoft Access discussion forum on the internet, with posts dating back more than 20 years, we have a wealth of knowledge and experience to share with you.

We're a friendly and helpful community, so don't hesitate to ask any questions you have or share your own experiences with Access. We're here to support you and help you get the most out of this powerful database program.

To get started, we recommend reading the post linked below. It contains important information for all new users of the forum:

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We hope you have a great time participating in the discussion and learning from other Access enthusiasts. We look forward to having you around!

 

[bob fitz]

Sounds like a job for DateDiff() function: https://learn.microsoft.com/en-us/o...ference/user-interface-help/datediff-function
To be of more help we'll need to know something of your table.

 

[Gasman]

Probably using this method.

Microsoft Access tips: Subquery basics

Using subqueries in Microsoft Access. What subqueries are, and examples of using subqueries.

allenbrowne.com

 

[The_Doc_Man]

Nobody else asked, so I will: Does each record have a date in it?

To do what you ask, it is necessary to have a relevant date within each record. If you do, then the suggestions above have a good chance to help you. If you have no date in the records, you can't compute such a thing, because there is no "inherent" date in an arbitrary record.

 

[Gasman]

You also need to get the order correct?

 

[theDBguy]

Hi @JimWY. Welcome to AWF!

 

[pbaldy]

Welcome. I moved your thread to a more appropriate forum.

 

[Pat Hartman]

Although either a subselect or a self join can be used to solve this problem, the solutions require a unique ID that can be used to order the records. The self join is more efficient than the subselect if that works for you because Access is very inefficient at evaluating sub queries.

The most efficient method is to use a report. With queries, the recordsets are moving targets which is why the calculation is so awkward and requires the use of unique sequence numbers to properly order the recordset but with reports, the process is straight sequential logic. You still have to sort the records but you don't need a unique sequence number since the records are processed sequentially rather than as a set. You can use the on Print event to calculate the difference between the current record and the previous record. You need to save each record as it is printed to the "previous" variable. If the previous variable is empty, then just save the current value as previous.

 

[sargon]

I try now Computer Zone Learning tutorial (How to Calculate Date Differences Between Consecutive Records in Microsoft Access)
from youtube...

Expr1: DMax("PaymentDate";"tblPayment";"PaymentDate]<#" & [PaymentDate] & "#")

But I receive an sintax error

 

[plog]

Every parenthesis, quote mark and bracket needs a partner. One of yours doesn't.

 

[sargon]

Expr1: DMax("PaymentDate";"tblPayment";"PaymentDate<#" & [PaymentDate] & "#")

still sintax error

 

[jdraw]

Calendar days? Business Days? Holidays included?

 

[The_Doc_Man]

@sargon - since we don't know how those things are defined, it is possible that you have incorrectly referenced them. However, traditionally, that statement would read:

Expr1: DMax( "[PaymentDate]", "tblPayment", "[PaymentDate] < #" & [PaymentDate] & "#" )

But I have to ask whether the third reference to PaymentDate (between ampersands) comes directly from a form or is that in a query context? It is possible that Access doesn't know where to get that value.

As a side note, you are certainly welcome to do what you did, but you often get better results by starting a new thread rather than tacking on to an older thread (such as this one which started in 2023.)

 

[Pat Hartman]

If the dates are not unique, your results will be inaccurate.

 

[sargon]

@The_Doc_Man,
It is from a query.

I am tring to replicate "How to Calculate Date Differences Between Consecutive Records in Microsoft Access" from Youtube channel Computer Learning Zone

 

[sargon]

Calendar days? Business Days? Holidays included?

Calendar days as total days (including holidays)

 

[sargon]

Here is an example

 

[arnelgp]

see your qryDays query now.

 

[sargon]

arnelgp, thank you, but it still had errors