Prev: 2007 Toolbars On Reports
Next: Top 3 by category group
From: cristin on 5 Apr 2010 23:47
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?
From: fredg on 6 Apr 2010 00:35
On Mon, 5 Apr 2010 20:47:01 -0700, cristin wrote:

> 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?

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")

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
 | 
Pages: 1
Prev: 2007 Toolbars On Reports
Next: Top 3 by category group