Set a date language in MS Access VBA

Question

I have a form in Access where I have a textbox which generates an email in Outlook. This email has to populate the date for a specific event. My PC language is French (Canada), so the month displays in French in the email while the rest is in English.

No matter which language the PC is in, I want the date to be in English.
i.e. 'December 19, 2022' or '19 December 2022', instead of '19 décembre 2022'.

Answer 1

You can use my function MonthNameInvariant:

' Constants:
    Public Const MaxMonthValue          As Integer = 12
    Public Const MinMonthValue          As Integer = 1


' Returns the English month name for the passed month number.
' Accepted numbers are 1 to 12. Other values will raise an error.
' If Abbreviate is True, the returned name is abbreviated.
'
' 2015-11-25. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function MonthNameInvariant( _
    ByVal Month As Long, _
    Optional ByVal Abbreviate As Boolean) _
    As String
    
    Const AbbreviatedLength As Integer = 3
    
    Dim MonthName( _
        MinMonthValue To _
        MaxMonthValue)      As String
    Dim Name                As String
    
    If Not IsMonth(Month) Then
        Err.Raise DtError.dtInvalidProcedureCallOrArgument
        Exit Function
    End If
    
    ' Non-localized (invariant) month names.
    MonthName(1) = "January"
    MonthName(2) = "February"
    MonthName(3) = "March"
    MonthName(4) = "April"
    MonthName(5) = "May"
    MonthName(6) = "June"
    MonthName(7) = "July"
    MonthName(8) = "August"
    MonthName(9) = "September"
    MonthName(10) = "October"
    MonthName(11) = "November"
    MonthName(12) = "December"
    
    If Abbreviate = True Then
        Name = Left(MonthName(Month), AbbreviatedLength)
    Else
        Name = MonthName(Month)
    End If
    
    MonthNameInvariant = Name

End Function

Full code and documentation in my library at GitHub: VBA.Date.

Addendum:

To create a text formatted as you have specified, you could use something like this:

Dim SendDate As Date
Dim TextDateInMail As String

' The date value in the mail to send.
SendDate = Date

' The formatted text date in the mail to send.
TextDateInMail = MonthNameInvariant(Month(SendDate)) & Format(SendDate, " dd, yyyy")

' Value of TextDateInMail:
' April 01, 2023