Formatting Quarters to 1st, 2nd, 3rd, 4th

  • Thread starter Thread starter Sherrie
  • Start date Start date
S

Sherrie

I know I can format a date to display in a quarter format
(i.e. 1,2,3,4 from a date of 8/19/03)using the "q" format,
but is there a way to add st, nd, rd, or 4th to the end
depending on which quarter it is?

For example format 8/19/03 to read 3rd quarter
Thanks,
Sherrie
 
A little function is all you need; you can call this from a query or another
function.
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg


'--------------Begin Code---------------------
Function FormatAddNumSuffix(varDate As Date, DateType As Byte) As String
Dim strNumSuffixCheckVal As String
Dim intDateVal As Integer
If IsNull(varDate) Then Exit Function
Select Case DateType
Case 1 'Days
intDateVal = DatePart("d", varDate)
strNumSuffixCheckVal = Right(CStr(intDateVal), 1)
Case 2 'Quarters
intDateVal = DatePart("q", varDate)
strNumSuffixCheckVal = CStr(intDateVal)
End Select
Select Case strNumSuffixCheckVal
Case "1"
strNumSuffixCheckVal = "st"
Case "2"
strNumSuffixCheckVal = "nd"
Case "3"
strNumSuffixCheckVal = "rd"
Case Else
strNumSuffixCheckVal = "th"
End Select
FormatAddNumSuffix = CStr(intDateVal) & strNumSuffixCheckVal
End Function
 
Oops, forgot about the teens. Here's a revised function
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

'---------------------------begin code---------------

Function FormatAddNumSuffix(varDate As Date, DateType As Byte) As String
Dim strNumSuffixCheckVal As String
Dim intDateVal As Integer
Select Case DateType
Case 1 'Days
intDateVal = DatePart("d", varDate)
strNumSuffixCheckVal = right(CStr(intDateVal), 1)
Case 2 'Quarters
intDateVal = DatePart("q", varDate)
strNumSuffixCheckVal = CStr(intDateVal)
End Select
Select Case strNumSuffixCheckVal
Case "1"
If intDateVal <> 11 Then
strNumSuffixCheckVal = "st"
Else
strNumSuffixCheckVal = "th"
End If
Case "2"
If intDateVal <> 12 Then
strNumSuffixCheckVal = "nd"
Else
strNumSuffixCheckVal = "th"
End If
Case "3"
If intDateVal <> 13 Then
strNumSuffixCheckVal = "rd"
Else
strNumSuffixCheckVal = "th"
End If
Case Else
strNumSuffixCheckVal = "th"
End Select
FormatAddNumSuffix = CStr(intDateVal) & strNumSuffixCheckVal
End Function
 
Back
Top