Date problem...

[Blackwidow]

Dat problem...

Much to my annoyance... the Head Secretary has now decided that having 9 September 2003 as a date is not good enough and she now wants 9th September 2003 etc.... ie.. all the st, rd, th's..

the only way I have found to do this is through a query.. where basically I tell it Date 1: IIf([Date 4]=22,"nd",IIf([Date 4]=1,"st",IIf([Date 4]=21,"st",IIf([Date 4]=31,"st",IIf([Date 4]=2,"nd",IIf([Date 4]=3,"rd",IIf([Date 4]=23,"rd","th")))))))


please someone tell me there is an easier way caus I have about five different dates to set this way.... and the query takes an age to run now...

 

[Mile-O]

Do NOT do it in a query. This will turn your date into a string and will render you unable to perform calculations with.

Public Function AppendDates(ByVal dteDate As Date) As String

    Dim strPart As String

    Select Case Day(dteDate)
        Case Is = 1, 21, 31
            strPart = "st"
        Case Is = 2, 22
            strPart = "nd"
        Case Is = 3, 23
            strPart = "rd"
        Case Else
            strPart = "th"
    End Select
        
    AppendDates = Day(dteDate) & strPart & " " & Format(dteDate, "mmmm") & " " & Year(dteDate)
    
End Function

is the function you want. Use it only for display on a form.

 

[Blackwidow]

will that then work when they use the data for mailmerging to word? Cause its for letters that are sent out?

 

[Mile-O]

In that case, if it's not for date calculations then having that code in a module and then, in your query:

Date 1: AppendDates([Date 4])

 

[Blackwidow]

Do you think that will work with the Exclusions screen that you helped me with???

 

[Mile-O]

It should.