Date format Like 1st, 2nd, 3rd, 4th.....of each month

[Nur Muhammad]

Dear Friend:
I want to apply the date formate into my report like:
1st of each month
2nd of each month
3rd of each month
4th of each month
5th or each month
.......
.........
.......
29th of each month
30th of each month

Here, the date will be used from a table: tblDPS, field name: Client ID,
Name, Installment Date. according to the Installment date, we will made the
report.

Pls help me solving my issue

Thanks

Nur Muhammad

 

[Al Campagna]

Nur
Try this method... using a date field called YourDate.

=Day([YourDate]) & IIf(Day([YourDate])=1 Or Day([YourDate])=21 Or
Day([YourDate])=31,"st",
IIf(Day([YourDate])=2 Or Day([YourDate])=22,"nd",IIf(Day([YourDate])=3 Or
Day([YourDate])=23,"rd","th"))) & "of each month"

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[John Spencer]

You will need a VBA function to do this. Here is one I copied from a posting
a while ago. I forgot to get the source so I can't credit the original author.

Public Function fOrdinalDate(DateIn)
Dim StrX As String

If IsDate(DateIn) = False Then
fOrdinalDate = DateIn
Exit Function
End If

StrX = CStr(Day(DateIn))
StrX = StrX & Nz(Choose(IIf((Abs(StrX) Mod 100) \ 10 = 1, 0, _
Abs(StrX)) Mod 10, "st", "nd", "rd"), "th")
StrX = StrX & " " & Format(DateIn, "mmmm yyyy")
fOrdinalDate = StrX

End Function

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County