Format date 1st, 2nd, 3rd

[Guest]

I am trying to format a date for a report. I would like it to display 1st of
June ,2006. The 1st, 2nd, 3rd is the part that is giving me problems. I
searched but, couldn't find answer, probably I am not using correct search
terms. Thank-you

 

[John Spencer]

Try searching on "ordinal" numbers to find ways to convert cardinal numbers
(1, 2, 4) to ordinal numbers (1st, 2nd, 4th)

You will need a function to do this. Here is some quick code (no error
handling) that should work for you.

Copy and paste this into a VBA code module.
'========= Code Starts =============
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
'=======++++++== Code Ends=============
You can use that function in a query calculated column

Field: OrdinalDate: fOrdinalDate([YourTable].[YourField])

or you can use it as the control source (warning name the control source
something other then the field name)

=fOrdinalDate([YourTable].[YourField])