How to sort query results by days of the week???????

[Tmconsult]

I have a report based on a query in A-97. I would like to be able to sort the results by day of the week. I have a field with the values M,T,W,H,F. Is there some way to sort it this way. Here is the SQL if this helps.

SELECT WeeklyResidentSchedule.*, Scheduling_Guidelines.Day, Scheduling_Guidelines.[AM/PM], Scheduling_Guidelines.Explanation
FROM WeeklyResidentSchedule LEFT JOIN Scheduling_Guidelines ON (WeeklyResidentSchedule.Program = Scheduling_Guidelines.Program) AND (WeeklyResidentSchedule.Year = Scheduling_Guidelines.Year) AND (WeeklyResidentSchedule.Service = Scheduling_Guidelines.Service);

 

[jtmach]

The easiest way to sort this would be to make a table that has the following
Day / DayNumber
M / 1
T / 2
W / 3
H / 4
F / 5

Then add that table to your query and sort by the DayNumber. The hardest questions always have the easiest answers.

 

[genomon]

Hi Tmconsult
I can think of a pretty convoluted way to do it. Create a new field in the query, i.e. "Sortfield". Populate it in the criteria box with something that returns a numeric ascending value, such as:
SELECT CASE [Scheduling_Guidelines].[day]
CASE "M"
Sortfield = 1 ...etc
Then click on the "Ascending" option in the sort box. I admit I have never tried the SELECT...CASE structure in criteria, but have had luck using SQL and operators such as "In" & "Not". The point is, you can create a defined field in your query to return a value, and then sort on it.
My first reaction was to use the IIf function, but didn't have time to explore it. Depending on your time, 2 other options present themselves also:
First, investigate if the Choose function is available in VBA (think it is in VB6) and try that, else I found some code on the DevX.com website for a user-defined function that could work for you (sorry it didn't paste well):
Function CompareValue(value As Variant, ParamArray valueList() As Variant) As _
Integer

' Comments : Compares a numeric or string value with a list of other values.
' : Returns the 1-based index of the matching value, or zero if the
' : value doesn't appear in the list.
' : String comparisons are case-sensitive.
' : This function can conveniently replace a Select Case or a list
' : of OR'ed comparison, e.g. you can replace the following line :
' : If s = "one" Or s = "Two" Or s = "Three" Then
' : with the more concise
' : If CompareValue(s, "One", "Two", "Three&quot Then
' Parameters : value - thing to be compared
' : valueList - list of things to be compared to
' Returns : Base 1 index of matching array item

' --------------------------------------------------------

Dim index As Integer
For index = 0 To UBound(valueList)
If value = valueList(index) Then
CompareValue = index + 1
Exit For
End If
Next
End Function

You can solve this fairly easily - good luck!!
-Geno

 

[BoxHead]

An alternative to a lookup table would be to type the following expression in the Field (top) line of a new column in the query and sort by this column.

IIf([Day]="M","1",IIf([Day]="T","2",IIf([Day]="W","3",IIf([Day]="H","4","5"))))

HTH John

Use what you have,
Learn what you can,
Create what you need.

 

[jebry]

Hi!

Just use the WeekDay function. Weekday(YourDate) returns the values Sunday = 1 through Saturday = 7. Using Weekday(YourDate, 2) returns Monday = 1 through Sunday = 7.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[CosmoKramer]

Add an expression field to your query something like this:

Format([yourDate],"w")

1 = Sunday, 2 = Monday, etc.. Sort it ascending and you should be all set

 

[BoxHead]

The great thing about access is that there are so many ways to do any one thing.

The horrific thing about access is that there never seems to be a simple answer to a question.

TM, I didn't notice an actual date field in your query, but if you do have one, I'd go along with Jeff or Cosmo.

John

Use what you have,
Learn what you can,
Create what you need.