Sorting records in the Report Design View

[christy726]

Currently, I use "Sorting and Grouping" tool in the Report Design View
to create group headers so that I can group my data accordingly.
However, I would like to know if I can apply a custom sort order.

For example, the field name that I want to group is "Region". I have
records of "East", "North", "South", and "West". Is there a way that
I can order by "North", "South", "East", and "West" instead? If I use
the "Sorting and Grouping" tool, it won't work because it only accepts
ascending or descending order.

I've heard about the Order By Property function but I'm not quite sure
how to apply in this situation. I'm not good at building events or
macros in Access. So I really appreciate if you can provide me
detailed guidelines.

Thanks for helping me out!

 

[Allen Browne]

It seems that your table has a Region field that should have one of 4
values. To ensure the data is valid, you need a Region table that has 4
valid records. Relating the tables together, this prevents bad entries such
as "Notrh".

If you have the Region table set up, you can add a field that tells it how
to sort the regions. The table will then have data like this:
RegionID SortOrder
====== =======
East 1
North 2
South 3
West 4

You can then create a query that uses both tables.
Include the SortOrder field in the query output.
Set the RecordSource of the report to the name of the query.
You can now use the SortOrder field in the report's Sorting'n'Grouping box.