Solved Formatted Concatenated Textbox (1 Viewer)

Tor_Fey

Registered User.
Local time
Today, 18:50
Joined
Feb 8, 2013
Messages
121
Morning All;

I have a textbox with the following:
Code:
I note that we have not received payment for: ref: " & [m_ref] & "/" & [m_folio] & " sent to you on the " &[datecreated]

the above displays the end date as 10/09/2020 instead of 10th September 2020, how can i add the required formatting to the Concatenated textbox?

I thought something like this: Format(Date(),"dd mmmm yyyy"), but i am unsure how i add my field to this string.

Your help as always is very much appropriated.

Kind Regards
Tor Fey
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:50
Joined
Feb 19, 2013
Messages
16,553
is the format function

format(datecreated,"d mmmm yyyy")

tho the 'th may be a problem and would require additional work
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:50
Joined
May 7, 2009
Messages
19,169
create first a function in a Module:
Code:
' http://vb-helper.com/howto_ordinal_extension.html
' Extend Date Number to Include Text Suffix
' Peter Chamberlin.
Public Function DateExtension(ByVal DateNum) As String
Dim DateRemainder As Integer

   ' Default to Most Common Extension
   DateExtension = "th"

   ' Handle ?11th through ?13th
   DateRemainder = DateNum Mod 100

   ' If Not An Exception
   If DateRemainder < 11 Or DateRemainder > 13 Then

      ' Get Right Digit
      Select Case DateNum Mod 10

         ' Update Appropriate Extension
         Case 1: DateExtension = "st"
         Case 2: DateExtension = "nd"
         Case 3: DateExtension = "rd"

      End Select

   End If

   ' Add Extension to Original Number
   DateExtension = DateNum & DateExtension

End Function

to show the formatted date:

DateExtension(day(date)) & format(date," mmmm yyyy")
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:50
Joined
Feb 19, 2013
Messages
16,553
copy and paste this little function into a new standard module

Code:
Function dateSuffix(dateVal As Date) As String
Dim s As String
s = Format(dateVal, "dd")
If Right(s, 1) > 0 And Right(s, 1) < 4 And Left(s, 1) <> 1 Then
     Select Case Right(s, 1)
        Case "1"
            s = s & "st"
        Case "2"
            s = s & "nd"
        Case "3"
            s = s & "rd"
       End Select
Else
    s = s & "th"
 End If

dateSuffix = s & " " & Format(dateVal, "mmmm yyyy")

End Function
 

Tor_Fey

Registered User.
Local time
Today, 18:50
Joined
Feb 8, 2013
Messages
121
Thanks everyone, i stumbled across the answer myself :)
 

Tor_Fey

Registered User.
Local time
Today, 18:50
Joined
Feb 8, 2013
Messages
121
is the format function

format(datecreated,"d mmmm yyyy")

tho the 'th may be a problem and would require additional work


I had found the same answer myself after posting my question :)

Code:
="I note that we have not received payment for: ref: " & [m_ref] & "/" & [m_folio] & " sent to you on the " & Format([datecreated],"d mmmm yyyy")

But as always thanks so much for your help on this.

Kind Regards
Tor Fey
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:50
Joined
May 7, 2009
Messages
19,169
If the day is 13, then datesuffix() will return:

13rd
 

Users who are viewing this thread

Top Bottom