Calculate Days Between Records (1 Viewer)
- Thread starter JimWY
- Start date
- Local time
- Today, 00:08
- Joined
- Sep 28, 1999
- Messages
- 7,398
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!
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
AWF VIP
- Local time
- Today, 00:08
- Joined
- May 23, 2011
- Messages
- 4,727
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.
To be of more help we'll need to know something of your table.
Gasman
Enthusiastic Amateur
- Local time
- Today, 00:08
- Joined
- Sep 21, 2011
- Messages
- 14,310
Probably using this method.
Microsoft Access tips: Subquery basics
Using subqueries in Microsoft Access. What subqueries are, and examples of using subqueries.
allenbrowne.com
- Local time
- Yesterday, 18:08
- Joined
- Feb 28, 2001
- Messages
- 27,189
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.
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.
- Local time
- Yesterday, 19:08
- Joined
- Feb 19, 2002
- Messages
- 43,293
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.
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.
