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'.
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
