Sorting on a Dayname string

[grenee]

Good day all.

I have a field of string Data type but it contains the day names of days of the week( Monday, Tuesday, ........). When I sort this field the values are not sorted Monday, Tuesday etc. What I get is Monday, Thursday, Tuesday, Wednesday.

I know why. Its because it does not recognize the days of the week, rather it sees strings.

However I want the impossible. I want the days sorted according to the days of the week.

Does anyone have a solution?

 

[pbaldy]

Include a field that returns the numeric value of the day (Weekday function may help) and sort on that.

 

[arnelgp]

alternative use a function, pass the field and sort on expression:

Public Function fnDayNameToInt(sDay As String) As Integer
Dim arr As Variant
Dim i As Integer
arr = Array("Saunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")
For i = 0 To UBound(arr)
If arr(i) = sDay Then
fnDayNameToInt = i + 1
Exit For
End If
Next
End Function


on your query/report/form:

select [field] from table order by fnDayNameToInt([field]);

 

[Galaxiom]

Let's not reinvent the wheel.

It is generally much faster to use a built in SQL function than calling a user defined function.

ORDER BY Switch([dayfield]="Monday",1,[dayfield]="Tuesday",2,[dayfield]="Wednesday",3,[dayfield]="Thursday",4,[dayfield]="Friday",5,[dayfield]="Saturday",6,[dayfield]="Sunday",7)

 

[arnelgp]

Let's not reinvent the wheel

i dont know of any existing function/method that do the same, do you?

 

[Galaxiom]

i dont know of any existing function/method that do the same, do you?

Switch() does exactly the same process as your function.

 

[grenee]

Well I am interested in learning about this function. It's the first time I am hearing about it and it looks like I would have uses for it along the way. So can you guide me where to place it.

 

[grenee]

I tried the week day function and it worked.