Sorting by day as a string

[owen]

I have a bunch of figures used in a report which are each associated with a day of the week. I want to sort these figures by the day. The day is a string literal 'Monday' through to 'Friday'. Can't use weekday as I don't use actual dates. Do I have to put the days into a table with a number field and sort by the number field?

 

[Brianwarnock]

You caould use Switch

Daynumber: Switch(yourdayfld="Monday",2,yourdayfld="Tuesday",3 etc


Brian

 

[raskew]

Hi -

Here's another possibility:

Public Function RtnDayNum(pstrDayName As String) As Integer
'*******************************************
'Purpose:   Returns day number (1 - 7)
'           when provided a complete or partial
'           Day name.
'Coded by:  raskew
'Input:     From debug (immediate) window:
'           1) ? RtnDayNum("Tuesday")
'           1) ? RtnDayNum("Fri")
'Output:    1) 3
'           2) 6
'*******************************************

Dim strHold  As String
Dim strDay As String
Dim intDay As Integer

   strDay = "SunMonTueWedThuFriSat"
   strHold = Left(pstrDayName, 3)
   intDay = InStr(strDay, strHold)
   RtnDayNum = intDay \ 3 + 1

End Function

HTH - Bob

P.S. You can use the same logic to return a month number when provided a month name:

Public Function RtnMonthNum(pstrMonName As String) As Integer
'*******************************************
'Purpose:   Returns month number (1 - 12)
'           when provided a complete or partial
'           month name.
'Coded by:  raskew
'Input:     From debug (immediate) window:
'           1) ? RtnMonthNum("April")
'           1) ? RtnMonthNum("Sep")
'Output:    1) 4
'           2) 9
'*******************************************

Dim strHold  As String
Dim strMonth As String
Dim intMonth As Integer

   strMonth = "JanFebMarAprMayJunJulAugSepOctNovDec"
   strHold = Left(pstrMonName, 3)
   intMonth = InStr(strMonth, strHold)
   RtnMonthNum = intMonth \ 3 + 1

End Function

 

[owen]

Wow. Thanks ever so for both great answers. I'll go and try now.