Custom Grouping??

[James T.]

Access 2000

I would like to group other than ascending or descending
and I have found that if I create a blank report in design
view, I can dump each separate report into the blank
report and get the "sorting" I desire, but the format is
wrong, I lose some headings.

Is there a way to do sorting that you can control other
than the above process I described?

I am trying to sort Apartments, Homes, Townhomes, Seniors
into this order: Homes, Townhomes, Senior and Apartments.

Any help appreciated.

Thanks,
James

 

[Rick B]

Why not put a nested IF statement in your sort field so that "Apartments" is
01, "Homes" is 02, "Townhomes" is 03, etc.?

Rick B

 

[Dirk Goldgar]

Why not put a nested IF statement in your sort field so that
"Apartments" is 01, "Homes" is 02, "Townhomes" is 03, etc.?

Or a Switch() function expression.

 

[James T.]

Thank you both for replying, but I am a little above a
newbie and am not sure what you guys are saying. Can
either of you give me an example that I could build from?

Thanks,
James

 

[Dirk Goldgar]

Thank you both for replying, but I am a little above a
newbie and am not sure what you guys are saying. Can
either of you give me an example that I could build from?

Thanks,
James

If you open the report's Sorting and Grouping dialog, you'll see the
columns "Field/Expression" and "Sort Order". You can enter an
expression in the Field/Expression column, preceded by an equals sign,
like this:

=Switch([Category]="Homes", 1, [Category]="Townhomes", 2,
[Category]="Seniors", 3, [Category]="Apartments", 4,
True, 5)

(where "Category" is standing in for the name of the field that contains
the values "Apartments", "Homes", "Townhomes", and "Seniors"). I've
broken that expression into separate lines for clarity, but it would all
be on one line in the Sorting and Grouping dialog. The last pair of
arguments, "True, 5", is just to catch any records that don't fall into
one of the predefined categories.

Then you would make sure that the Sort Order column says "ascending",
and there you have your custom sort order. You can choose to have a
group header and/or footer for this expression or not.