Sorting

[andrewneal]

I created a report that lists names in alphabetical order according to their status. The status choices are MSP, THP, VET, NAF and OTH. These are chosen from a dropdown menu in the form which pulls this list from a status table. In the report I must list them in the order above but without putting numbers in the front of each like 1 MSP, 2 THP, 3 VET, ect it puts them in alphabectical order. MSP personnel must be listed first then THP then VET and so on. NAF and OTH are to be "intermingled" as these personnel do not have a hiring preference and should be considered equal, but have to be identified in the form for possible future seperation of these two status categories. Anyway, the supervisor does not like the idea of having the "numbers" in front of the MSP, THP, VET and so on. Is there a VBA code I could write to accomplish this and list them in a different order according to how I want? I attached a copy of the report to get a better idea of what I am talking about...

 

[Pat Hartman]

If you have a table that defines this code list, add a column and populate it with numbers that will order the list as you want it. Join to this table in the report's RecordSource query so you can obtain the sortField. Add the SortField to the sorting and grouping properties.

 

[andrewneal]

There are 5 total options for them to choose from for "status." I thought about this option, but how would I ensure that numbers 4 & 5 are intermingled together and not separated when I assign these numbers in the table? The problem came in when 4 & 5 were also separated into their prospective sections but the Human Resourses Officer wants to make sure that they are counted as "the same" in the hiring process; this means there must be 5 "status" choices but only 4 "statuses" listed on the report (with 4 & 5 intermingled in alphabectical order by last name). Hmmm She wants the "MSP, THP, VET, NAF & OTH" to be choices when entering names but wants the groupings on the report to be "MSP, THP, VET & NAF/OTH" rather than having NAF & OTH separated...

 

[Pat Hartman]

Then you need two codes. One to indicate group and the second to indicate sequence within group. So you have three groups with only one member each and a fourth group with two members.