how do i format a date to add st/nd/rd/th 1

[liaison]

Hello, i am fairly new to access and am just having trouble with a query,

the result i am trying to achieve is to have a date automaticly formatted to add the st/rd/th i.e. the 12/04/1964 formated to 12th Apr 1964 as this is to be added to a report for all the employees in our company.

this is what i have so far:

 DOB: CStr(Format([BirthDate],"dd")) & "-" & CStr(Format([BirthDate],"mmm")) & "-" & CStr(Format([BirthDate],"yyyy"))

my gut feeling is telling me to use a nested IIf statement???? any help would to greatly received!

 

[M8KWR]

try using

Format(BirthDate, "dd-mmm-yyyy")

the only thing that is wrong with this is does not display the "th" or "nd" or "rd" etc after the day.

 

[CosmoKramer]

liaison,

Paste the following function into a new module:

Public Function FormatDate(fDate As Date) As String
  
  Select Case Day(fDate)
    Case 1, 21, 31
      FormatDate = Format(fDate, "d\s\t mmmm yyyy")
    Case 2, 22
      FormatDate = Format(fDate, "d\n\d mmmm yyyy")
    Case 3, 23
      FormatDate = Format(fDate, "d\r\d mmmm yyyy")
    Case Else
      FormatDate = Format(fDate, "d\t\h mmmm yyyy")
  End Select
End Function

Add a new column to your query that will use this function to format your date field. Something like:

DateFormatted: FormatDate([BirthDate])

Hoc nomen meum verum non est.

 

[dhookom]

Great solution/function CosmoKramer. If the OP wants just Apr rather than April, change the "mmmm" to "mmm" in the format.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[liaison]

Thank You CosmoKramer, Just what i needed!!!

To add text either side do i add that in the vba window or as part of the query????

 

[CosmoKramer]

I would suggest the query or the text box of the report. The function should be left alone in case you want to use it somewhere else in your database

Hoc nomen meum verum non est.

 

[liaison]

Great! Thank You!