Complex Sort

[Jojojo]

I'm wondering if this is even possible....



I have the following query:
Classes: [EventName] & ": " & [ClassDay] & "'s at " & [StartTime]
Sort: Ascending

It produces the following:
Kickboxing: Friday's at 5:00pm
Kickboxing: Monday's at 5:00pm
Kickboxing: Wednesday's at 5:00pm

Is there any I can sort it, so that is still sorts ascending, class name first, but then by day of the week? So that Monday classes come first, then Tuesday etc? And if so, can I do the same thing with the time? I can't split apart the fields, I need them to show as above in 1 combo box.

Thanks!

[pbaldy]

I'd probably include the fields separately in the query, and sort on them. They can be hidden so the user doesn't see them. You will need the numeric equivalent of the day, rather than the text.

[Jojojo]

I am having problems getting the numeric value of the day to work. When a class is set up - the user selects what day of the week the class runs on, ex: Monday, Tuesday etc- via combo box. The weekday, weekdayname etc functions - from what I am reading work on a actual date entered. I've tried a few variations of them and when they do give me a number - its the same number regardless of what the user selected - so I will see all 2's etc.

I'm not sure what function to use so that is the ClassDay entered is Monday - it returns 1, if Tuesday = 2 etc...

[pbaldy]

I can't think offhand of a way to get from name to number with a function. You can have a table that associates name and number and join it in this query, which will let you get the number.

[June7]

As pbaldy said, include the fields individuallly in query, but just as sort criteria, don't display. Keep the concatenated value for display. Use Weekday() function to get the day of week number and use as sort criteria.

[pbaldy]

OP has already mentioned that the Weekday function doesn't work, which is not surprising if the day is stored as "Monday" as it appears to be.

[June7]

Oh, I missed that, assumed there was a date field that could be referenced. Then can use a custom function or nested IIF or Switch Function, like:

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

[Jojojo]

Thank you both - June7: that worked!!!!!