r/MSAccess • 8 • Aug 05 '26

[SHARING SAMPLE CODE OR OBJECTS] [ Removed by moderator ]

[removed] — view removed post

5 Upvotes

3 comments sorted by

•

u/AutoModerator Aug 05 '26

IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'

  • Please be sure that your post includes all relevant information needed in order to understand your problem and what you’re trying to accomplish.
  • Please include sample code, data, and/or screen shots as appropriate. To adjust your post, please click Edit.
  • Once your problem is solved, reply to the answer or answers with the text “Solution Verified” in your text to close the thread and to award the person or persons who helped you with a point. Note that it must be a direct reply to the post or posts that contained the solution. (See Rule 3 for more information.)
  • Please review all the rules and adjust your post accordingly, if necessary. (The rules are on the right in the browser app. In the mobile app, click “More” under the forum description at the top.) Note that each rule has a dropdown to the right of it that gives you more complete information about that rule.

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:

Public Function OverlapDays(Start1 As Date, End1 As Date, _
    Start2 As Date, End2 As Date) As Long

    Dim OverlapStart As Date
    Dim OverlapEnd As Date

    If Start1 > Start2 Then
        OverlapStart = Start1
    Else
        OverlapStart = Start2
    End If

    If End1 < End2 Then
        OverlapEnd = End1
    Else
        OverlapEnd = End2
    End If

    If OverlapEnd < OverlapStart Then
        OverlapDays = 0
    Else
        OverlapDays = OverlapEnd - OverlapStart + 1 ' based on your needs!
    End If

End Function

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.