• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Solved Formatted Concatenated Textbox (1 Viewer)

Tor_Fey

Registered User.
Local time
Today, 16:34
Joined
Feb 8, 2013
Messages
112
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, 16:34
Joined
Feb 19, 2013
Messages
12,538
is the format function

format(datecreated,"d mmmm yyyy")

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

arnelgp

error reading drive A:
Local time
Today, 23:34
Joined
May 7, 2009
Messages
10,556
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, 16:34
Joined
Feb 19, 2013
Messages
12,538
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, 16:34
Joined
Feb 8, 2013
Messages
112
Thanks everyone, i stumbled across the answer myself :)
 

Tor_Fey

Registered User.
Local time
Today, 16:34
Joined
Feb 8, 2013
Messages
112
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

error reading drive A:
Local time
Today, 23:34
Joined
May 7, 2009
Messages
10,556
If the day is 13, then datesuffix() will return:

13rd
 

Users who are viewing this thread

Top Bottom