Date formatting problem

  • Thread starter Thread starter dkv98446
  • Start date Start date
D

dkv98446

I need to print a "Certificate of Completion" where the date is in the
following format:

1st day of March, 2007
2nd day of March 2007
3rd day of March 2007
4th day of March 2007
5th day of March 2007
21st day
22nd
23rd
24th
31st

The format: d"th Day of "mmmm yyyy comes the closest, but doesn't give me
what I want. I need to replace the 'th' with a variable. I have thought
about creating a table of suffixes based on the day of the month, but I'm
not sure how to string the table lookup results and the rest of the date. I
also feel that there has to be a simpler method.


Thanks for the help!!
 
I need to print a "Certificate of Completion" where the date is in the
following format:

1st day of March, 2007
2nd day of March 2007
3rd day of March 2007
4th day of March 2007
5th day of March 2007
21st day
22nd
23rd
24th
31st

The format: d"th Day of "mmmm yyyy comes the closest, but doesn't give me
what I want. I need to replace the 'th' with a variable. I have thought
about creating a table of suffixes based on the day of the month, but I'm
not sure how to string the table lookup results and the rest of the date. I
also feel that there has to be a simpler method.

Thanks for the help!!
Click to expand...

Place the following function in a module (watch out for word wrap on
the longer lines):

Public Function DateOrdinalEnding(DateIn, MoIn As String)
' Will convert a Date (10/2/2006) to
' 2nd day of October 2006

' MoIn determines Month Format, i.e. "mmm" for "Oct" or "mmmm" for
"October"

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

DateOrdinalEnding = dteX & " day of " & Format(DateIn, MoIn & " yyyy")


End Function
==========

Then call the function from a query:

TextOrdinal:DateOrdinalEnding([DateField],"mmmm")

or from a report or form undound control:
= DateOrdinalEnding([DateField],"mmmm")
 
Thanks for the quick response and help!!

fredg said:
I need to print a "Certificate of Completion" where the date is in the
following format:

1st day of March, 2007
2nd day of March 2007
3rd day of March 2007
4th day of March 2007
5th day of March 2007
21st day
22nd
23rd
24th
31st

The format: d"th Day of "mmmm yyyy comes the closest, but doesn't give me
what I want. I need to replace the 'th' with a variable. I have thought
about creating a table of suffixes based on the day of the month, but I'm
not sure how to string the table lookup results and the rest of the date.
I
also feel that there has to be a simpler method.

Thanks for the help!!
Click to expand...

Place the following function in a module (watch out for word wrap on
the longer lines):

Public Function DateOrdinalEnding(DateIn, MoIn As String)
' Will convert a Date (10/2/2006) to
' 2nd day of October 2006

' MoIn determines Month Format, i.e. "mmm" for "Oct" or "mmmm" for
"October"

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

DateOrdinalEnding = dteX & " day of " & Format(DateIn, MoIn & " yyyy")


End Function
==========

Then call the function from a query:

TextOrdinal:DateOrdinalEnding([DateField],"mmmm")

or from a report or form undound control:
= DateOrdinalEnding([DateField],"mmmm")
Click to expand...
 
Back
Top