Standardizing date format when moving code to different machines in MS Access

Question

Good day everyone, I have a MS Access function that I was using on a machine using the mm/dd/yyyy format but now have moved to a machine using the dd/mm/yyyy format.

Public Function fncERates(sEID As Long, PeriodEnd As Date) As Double

Dim strERates As String
Dim strPeriodEnd As String

PeriodEnd = CDate(Nz(PeriodEnd, 0))

strPeriodEnd = "#" & Format(PeriodEnd, "dd/mm/yyyy") & "#"

Debug.Print strPeriodEnd

strERates = "SELECT TOP 1 [ERate] FROM EmpStatus WHERE [EffDate] <= " & strPeriodEnd & " And EID = " & sEID & "" _
            & " ORDER BY [EffDate] DESC"
            
    With CurrentDb.OpenRecordset(strERates)
        If Not (.BOF And .EOF) Then
            fncERates = .Fields(0)
        End If
    End With

End Function

What it does is it returns the current Employee's pay rate based on the ending period. On debugging, fncERates(36,#12/13/21#) returns the correct rate ($25.00) but fncERates(36,#13/12/21#) returns 0 and Access sees #13/12/21# as 21/12/2013.

What can I do to fix the code to be universally read by different computers no matter the system's date format?

I am using Ms Access 2007 and Ms Access 2016.

Access uses the 'mm/dd/yyyy' format regardless of location , so any queries/sql scripts must adhere to this format. — Kostas K., Dec 13 '21 at 15:21
Can you please explain why Access sees #13/12/21# as 21/12/2013? — newbieDB Builder, Dec 13 '21 at 15:38

score 0 · Accepted Answer · answered Dec 13 '21 at 15:47

A Date value cannot be Null, so you can reduce it to this - using the universal ISO sequence for the string expression for the date value:

Public Function fncERates(sEID As Long, PeriodEnd As Date) As Double

    Dim strERates As String
    Dim strPeriodEnd As String

    strPeriodEnd = "#" & Format(PeriodEnd, "yyyy\/mm\/dd") & "#"

    Debug.Print strPeriodEnd

    strERates = "SELECT TOP 1 [ERate] FROM EmpStatus WHERE [EffDate] <= " & strPeriodEnd & " And EID = " & sEID & "" _
                & " ORDER BY [EffDate] DESC"
            
    With CurrentDb.OpenRecordset(strERates)
        If Not (.BOF And .EOF) Then
            fncERates = .Fields(0)
        End If
    End With

End Function

why Access sees #13/12/21# as 21/12/2013?

Because Access tries to be smart. For some reason, it sees 21 as the day, and as no month of 13 exists, that could be the year. Thus, alway use four-digit years and, in code, the ISO sequence as this is understood correctly everywhere.

Ok, understood. Thanks. – newbieDB Builder Dec 13 '21 at 16:07 — newbieDB Builder, Dec 13 '21 at 16:07

Standardizing date format when moving code to different machines in MS Access

1 Answers1