two sorts in one report

[Leathem]

I have a report which sorts a list of names into two groups based on one field ("confirmed" or "waitlisted"), but I'd like to sort the confirmed names alphabetically by name and the waitlisted names by a different field ("draw number"). I seem to be able to sort the entire list by one key or the other, but not the different keys for the different groups. Is there a way to do that?

 

[vbaInet]

... but I'd like to sort the confirmed names alphabetically by name and the waitlisted names by a different field ("draw number").

Right, I don't understand this. You can't sort a field based on another field. You can sort a set of records based on a field. Clarify.

Let's see a couple of Values from the relevant fields - Confirmed, Waitlisted and the other field you mentioned above.

 

[Leathem]

Sorry I'm unclear on this. The query that the report is based on has (among others) three fields: Name, Draw and Confirmed. So associated with each Name is a Draw number and either a C or a W in the Confirmed field. In the report I sort first by the Confirmed field, which gives me the names separated into two 'groups' since all the C's come first. My second sort uses the Name field and sorts alphabetically, meaning that while the 'groups' are still separated into C's and W's, both are sorted alphabetically. But what I want is for the C's to remain alphabetical, but I'd like to sort the names in the W group by the Draw field (integers). I hope this makes it clearer, and thanks for the help.

 

[vbaInet]

I see what you mean. The main problem here is that we have to deal with two data types because we don't want an ASCII sort applied to the numbers, so we need two sort fields. Try these as alias fields in your report's record source:

NameSort: IIF([Confirmed] = "C", [Name], Null)

DrawSort: IIF([Confirmed] = "W", [Draw], Null)

Sort Order:
Confirmed > NameSort > DrawSort

 

[Leathem]

Fantastic! Worked like a charm. I can't thank you enough for the insight.

 

[vbaInet]

Happy to hear!