Format Date in Report

  • Thread starter Thread starter Roger Bell
  • Start date Start date
R

Roger Bell

I have a report (Certificate) which I would like the date to convert from say
2.2.10 to 2nd February 2010. The Field on the Report is [Date of Request]

Could someone kindly reply with the correct syntax to include the formatting
in this text Box?

Also, if say the date is 3.2.10. can this be converted to 3rd February 2010,
ie can you tell Access to record 2nd, 3rd etc?

Many thanks for any help
 
Access doesn't have the ability to put the ordinal suffixes onto numbers.
You'll have to write your own function to do that, something like:

Function FormatOrdinalDate(InputValue As Variant) As String
Dim strDay As String

If IsDate(InputValue) Then
Select Case Day(InputValue)
Case 1, 21, 31
strDay = Day(InputValue) & "st"
Case 2, 22
strDay = Day(InputValue) & "nd"
Case 3, 23
strDay = Day(InputValue) & "rd"
Case Else
strDay = Day(InputValue) & "th"
End Select
FormatOrdinalDate = strDay & " " & _
Format(InputValue, "mmmm yyyy")
End If

End Function
 
Back
Top