Technical Advisory: Resolving ISO Week Number Discrepancies in Access and VBA

🚀 Overview

In Microsoft Access and other environments utilizing Visual Basic for Applications (VBA), a specific logic error exists when calculating week numbers according to the ISO 8601 standard. IT Administrators and developers should be aware that the standard Format() and DatePart() functions can return an incorrect value of “53” for the last Monday of certain calendar years, when the correct ISO-compliant value should be “1.” This anomaly occurs because the underlying system calls to Oleaut32.dll fail to correctly transition the final days of December into the first week of the subsequent year.

⚙️ Key Technical Details

• Affected Syntax: The issue manifests when using the ISO 8601 parameters:
  
  Format(DateValue, "ww", vbMonday, vbFirstFourDays)

DatePart("ww", DateValue, vbMonday, vbFirstFourDays)
• The Root Cause: Almost all date-related functionality in VBA (excluding DateSerial) relies on the Oleaut32.dll system library. A legacy bug within this DLL causes the week-numbering logic to misidentify the crossover point for years where the last Monday falls in the very last days of December.
• ISO 8601 Compliance: By definition, a calendar week starts on Monday and Week 1 is the week containing the first Thursday of the year (or January 4th). The bug incorrectly assigns the last Monday to a 53rd week of the current year instead of the 1st week of the new year.
• Identifying “Problem” Dates: Examples of affected dates include 12/29/2003, 12/31/1855, and 12/30/1895. In these instances, the functions return Week 53, but the standard requires them to be Week 1.

🛡️ Resolution and Workarounds

Since the bug is embedded in a core system DLL, the resolution requires implementing custom logic to override the default function behavior. There are two primary ways to address this:

1. The Logic Check Wrapper

You can wrap the standard function in a conditional check. If the function returns 53, the code checks the week number of seven days later; if that result is 2, it forces the current date to return 1.

Function ISO_WeekNumber(AnyDate As Date) As Integer
    Dim Result As Integer
    Result = DatePart("ww", AnyDate, vbMonday, vbFirstFourDays)
    If Result > 52 Then
        If DatePart("ww", AnyDate + 7, vbMonday, vbFirstFourDays) = 2 Then
            Result = 1
        End If
    End If
    ISO_WeekNumber = Result
End Function
    

2. The Comprehensive User-Defined Function (UDF)

For a more robust solution that avoids Oleaut32.dll‘s week calculation entirely, administrators can deploy a function that manually calculates the week based on the Thursday-rule (ISO 8601). This involves calculating the total days elapsed in the year and determining if the year’s start and end dates fall on a Thursday, which is the only condition under which a 53rd week is valid.

⚠️ Impact

This discrepancy primarily impacts organizations that rely on precise ISO-standardized reporting for logistics, supply chain management, or financial cycles—especially those operating in Europe where ISO 8601 is the default.

• Data Integrity: Automated reports grouped by “Week Number” will show split data for the transition week, leading to inaccurate year-over-year comparisons.
• Scheduling Errors: Maintenance scripts or automated tasks triggered by week numbers may run at the wrong time or fail to trigger during the transition between December and January.
• Legacy Systems: Many mission-critical Access databases have been in production for decades; administrators should audit any application that performs date-based calculations to ensure these custom workarounds are implemented.

📅 Official Source: Read the full article on Microsoft.com