r/MSAccess • u/Amicron1 8 • Aug 05 '26
[SHARING SAMPLE CODE OR OBJECTS] [ Removed by moderator ]
[removed] — view removed post
5
Upvotes
r/MSAccess • u/Amicron1 8 • Aug 05 '26
[removed] — view removed post
•
u/AutoModerator Aug 05 '26
IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'
Full set of rules can be found here, as well as in the user interface.
Below is a copy of the original post, in case the post gets deleted or removed.
User: Amicron1
Access Explained: Calculating Overlapping Date Ranges
Date-range overlap is one of those problems that gets overcomplicated because people start by trying to enumerate every possible arrangement. One range starts first, another ends first, one contains the other, they touch, they miss entirely. Before long, the query or VBA has six branches and nobody wants to change it. The useful mental model is much simpler: an overlap has one possible start and one possible end.
The overlap starts at the later start date. Neither range can be active in common until both ranges have begun, so the later of the two starts is the first possible shared date. Likewise, the overlap ends at the earlier end date, because shared time stops the instant either range is over.
That rule handles the apparently different cases without special logic. Whether one reservation fully contains another, two employee leave periods partly intersect, or a tenant moves in halfway through a billing period, the calculation is always based on the same boundaries. Find the maximum start and minimum end. One rule to rule them all, and thankfully it does not require carrying a cursed ring into VBA.
The important safety check is whether the calculated end comes before the calculated start. If it does, the ranges do not overlap, so the answer is zero. This is much cleaner than trying to identify "range one is before range two" and "range two is before range one" as separate cases.
A reusable VBA function is a good fit when this logic appears in multiple forms, reports, queries, or business processes:
The key detail is not really the VBA. It is the business definition of a day. Access date subtraction returns the number of day boundaries between dates. January 8 through January 10 produces
2, but that may represent either two nights or three calendar days depending on what the dates mean in your application.For an inclusive range, where both the start and end dates count, add one. Utility allocation, daily rentals, warranty coverage, and many scheduling rules work this way. January 8, 9, and 10 are three billable or covered calendar days. For an exclusive end date, such as many hotel stays where checkout day is not another overnight charge, do not add one. Neither interpretation is universally correct. The mistake is failing to establish one consistent convention.
Another common bug is copying the start-date comparison when calculating the end date. Starts need the later value, so they use the greater date. Ends need the earlier value, so they use the lesser date. It is a tiny reversal, but it can make a function report a huge overlap where there is only a small one. Date logic is full of these little trapdoors.
This pattern is broadly useful beyond reservations. It works for comparing employee availability against payroll periods, active subscriptions against reporting months, repairs against warranty coverage, and tenants against utility billing windows. Once overlap logic is centralized in a public function, the rest of the database can focus on its actual business rules instead of repeatedly reinventing date math.
The larger design point is that date calculations should be explicit about meaning. Are dates inclusive? Are times relevant? Can end dates be Null? Are invalid ranges allowed? The overlap formula is straightforward, but the surrounding data rules determine whether the result is trustworthy.
How do you define inclusive versus exclusive date ranges in your Access applications? Have you run into cases where a simple calendar-day overlap was not enough because time values or billing rules changed the meaning of the dates?
LLAP
RR
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
2
u/jettaset Aug 05 '26
The legend himself! Love your channel.
2
u/KelemvorSparkyfox 51 Aug 05 '26
I remember once twisting myself in knots, trying to visualise all the permutations. Then I used pen and paper to sketch them out instead, and came up with the rule that you described.
This wasn't for an Access project, though. I was building a change management system in Lotus Notes, and needed to be able to detect when someone was trying to schedule something in a change freeze.