how can i format a date that uses "1st" or "2nd" or "3rd" or 4th".

  • Thread starter Thread starter cristin
  • Start date Start date
C

cristin

how can i format a date that uses "1st" or "2nd" or "3rd" or 4th"... i am
working on a program that generates certificates. i want to use dates such
as "3rd of April 2010". when i run my program the words that appears onthe
screen was 3-Apr-10. can you help me solve this problem?
 
how can i format a date that uses "1st" or "2nd" or "3rd" or 4th"... i am
working on a program that generates certificates. i want to use dates such
as "3rd of April 2010". when i run my program the words that appears onthe
screen was 3-Apr-10. can you help me solve this problem?
Click to expand...

You can call this function.
Copy and paste it into a Module. Watch out for word wrap on the longer
lines.
I've set it to return the date in the form of
5th of April, 2010
for today's date.
You can also display the date in several other formats. Simply comment
out the current line of code and un-comment one of the other lines, as
wanted.

Public Function DateOrdinalEnding(DateIn, MoIn As String)
' Will add an Ordinal ending to a date
' i.e. Novermber 13th, 2000
' MoIn determines Month Format, i.e. "mmm" for "Feb" or "mmmm" for
"February"

If IsNull(DateIn) Then
DateOrdinalEnding = ""
Exit Function
End If
Dim dteX As String

dteX = DatePart("d", DateIn)

dteX = dteX & Nz(Choose(IIf((Abs(dteX) Mod 100) \ 10 = 1, 0,
Abs(dteX)) Mod 10, "st", "nd", "rd"), "th")

' November 13th, 2000
' DateOrdinalEnding = Format(DateIn, MoIn) & " " & dteX & ", " &
Format(DateIn, "yyyy")
' **********************
' Tuesday, November 25th, 2008
' DateOrdinalEnding = Format(DateIn, "dddd, " & MoIn) & " " & dteX &
", " & Format(DateIn, "yyyy")
' **********************
' 17th day of September 2003'
'DateOrdinalEnding = dteX & " day of " & Format(DateIn, " " & MoIn & "
yyyy")
'***********************
' Friday 4th of July, 2008
' DateOrdinalEnding = Format(DateIn, "dddd") & " " & dteX & " of " &
Format(DateIn, " " & MoIn & ", yyyy")
'***********************
' Friday 4th July, 2008
' DateOrdinalEnding = Format(DateIn, "dddd") & " " & dteX & " " &
Format(DateIn, " " & MoIn & ", yyyy")
' **********************
' 4th of July, 2008
DateOrdinalEnding = dteX & " of " & Format(DateIn, " " & MoIn & ",
yyyy")

End Function

Call it from a query
Exp: DateOrdinalEnding([DateField],"mmmm")
or directly in the control source of an unbound control, on a form or
in a report:
= DateOrdinalEnding([DateField],"mmmm")
 
Back
Top