Sort Order

[Guest]

I'd like to sort reports by lastname, with the exception being "if lastname =
Open" (open position), sort at the end of the group. How do I denote that
and there does that go?

Thanks for any info or other suggestions as to how to get open positions
listed at the end of a departmental list.

KK

 

[Rick B]

In your sorting and grouping control in your report, add a new grouping
level of...

=lastname="open"


Then the second grouping level would be...

=lastname

 

[Guest]

Base your report on a query which includes a computed column with an
expression which returns a value if LastName = "Open" which will always be
last in the sort order, e.g.

SELECT *, IIf([LastName]="Open","ZZZZ",[LastName]) AS SortByMe
FROM YourTable;

The IIF function will return "ZZZZ" for those rows where LastName = "Open"
and the value of LastName otherwise. Sort your report on the SortByMe
column, but don't include it in the report of course.

To do this in query design view you'd enter the following in the 'field' row
of a blank column in the query design grid:

SortByMe: IIf([Lastname]="Open","ZZZZ",[Lastname])

Make sure the 'Show' checkbox for the column is checked. Don't sort on this
column in the query, however; do it in the report design.

 

[Guest]

This seems to make sense, but it's not working yet...

In my sorting and grouping control, I have a couple of sorts before these
unrelated to name. Then I have.....

=[lastname]="Open"
=[lastName]

.......in that order.

Both are set to ascending order.
When I sort, I they don't come up alphabetical at all. When I reverse the
order, it sorts like it did previously, with "Open" positions in the middle
of the list.

What am I doing wrong??? Thanks!

KK