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.

 

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

 

[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

 

[Gasman]

Worked for me?

Perhaps due to your date delimiters?

If I use yours I get.

So change the code to match your date format.
Could try a universal format?

SELECT tblPayment.PaymentID, tblPayment.PaymentDate, DMax("PaymentDate","tblPayment","PaymentDate < #" & Format$([PaymentDate],"yyyy-mm-dd") & "#") AS PrevPaymentDate, Nz(DateDiff("d",DMax("PaymentDate","tblPayment","PaymentDate < #" & Format$([PaymentDate],"yyyy-mm-dd") & "#"),[PaymentDate]),0) AS [Difference(days)]
FROM tblPayment;

 

[sargon]

You are wright. Now it is working