If statement for sort order

[hamrthroer]

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).

 

[pbaldy]

Wouldn't you just give them the same number?

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

 

[hamrthroer]

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

Switch funtion? Could you elaborate?

 

[pbaldy]

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.

 

[SOS]

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

 

[hamrthroer]

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?

 

[pbaldy]

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.

 

[hamrthroer]

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!