Query Long Format Date

[Darrell Wootton]

Hi,

Pulling records from db to show course joining instructions, then merged into word.

I need help with query to show dates:

ie: Monday 21st August 2004

I can sort out everything except 21st, 4th, 2nd etc

StartDateDD = Format([StartDate],"DD") Already in Right Format

DateAbrev:IIf([StartDateDD]="01" Or "21" Or "31","st",(IIf ([DayCourseStartDate]="02" Or "22", "nd", etc etc etc))

Trying to get the right format but not quite there.

Thanks

Daz...

 

[Mile-O]

I'd just write a VBA function and call it from the query:
In this case it would be faster as you are having to evaluate all sides of the nested IIf.



Put this in a standalone module:

Public Function DateSuffix(strDatePart As String) As String
    Select Case strDatePart
        Case Is = "01", "21", "31"
            DateSuffix = "st"
        Case Is = "02", "22"
            DateSuffix = "nd"
        Case Is = "03", "23"
            DateSuffix = "rd"
        Case Else
            DateSuffix = "th"
    End Select
End Function

Then in your query:

DateAbrev: DateSuffix([StartDate])

 

[Darrell Wootton]

Thanks for the reply, I will try it.

Daz...