Good afternoon,

I have a report which sorts rank using an IF statement. Looks like this:

SELECT LastName, FirstName, IIf(IsNull([MI]),"",[MI] & ". ") AS Expr1, Rank, Shift, StateEntryDate, Random, PromoDate, DaysOff, IIF([Rank]="COII", "0", IIF([Rank]="COI", "1", IIF([Rank]="COS", "2", IIF([Rank]="COC", "3", IIF([Rank]="COL", "4", IIF([Rank]="COM", "5", "")))))) As SortOrder


I have to split it up to show COI, COII AND COS as one group and the others as individual rankings (such as it does now for all ranks).

Wouldn't you just give them the same number?

You may find the Switch() function easier to work with.

tried the same number. It still sorts them just doesn't give them a lable.

Switch funtion? Could you elaborate?

What label should it give them? I don't see anything in your existing SQL that would label them.

Look in help at the Switch() function. Post back if something there isn't clear.

Pardon me for jumping in here, but shouldn't there be an ORDER BY clause in the SQL statement?

The lable is the rank so if the rank is COI it would put them in the order specified and group the staff under COI and so on. Could I specify in the query that COI, COII AND COS are labled something different like LineStaff?

Pardon me for jumping in here, but shouldn't there be an ORDER BY clause in the SQL statement?

An ORDER BY clause would be irrelevant for a query used in a report. Sorting would be handled in Sorting and Grouping. (Plus OP only posted the SELECT clause; there may be an ORDER BY clause)

I guess I'm not clear on what you want here. If you want those 3 to group together on the report, I think you would need another field in the query to group on, and put your label there.

I got it worked out. I seperated the report in two and took the micky mouse approach with a dialog box and command buttons. I have alot of changes and have to move on. Next I have to export a report into an excel spreadsheet. Should be torture.

Thanks for all of your help!