We help IT Professionals succeed at work.

Report Grouping and setting a sort order

pdvsa
pdvsa asked
on
Hello, I have a report that has a group
Past Due
Completed
Pending

If I sort alphabetically it will start with Completed the. Past Due the Pending.

How can I order it according to the above?
Comment
Watch Question

Add another field where Past Due = 1, Completed = 2 and Pending = 3. Then sort on that field.

Flyster
pdvsaProject finance

Author

Commented:
another field in the table or
make something in the query ie:  Sort:  Past Due = 1, Completed = 2 and Pending = 3

thank you
Are you actually storing one of the three text values ("Past Due", "Completed" or "Pending") in your table?

A better solution is to have a numeric Status field, related to a table of StatusValues.  You can then choose the status name from a drop-down list and store its corresponding value in your table.  One of the fields in the StatusValues table could be a ReportOrder field with a numeric value corresponding to the order which you want the groups to appear in the report.

If you wish to keep the text values in your table, then use the Switch function to convert the text to a numeric value.  Add a field to your RecordSource query as follows:

ReportOrder: Switch ( [StatusField]="Past Due", 1, [StatusField]="Completed", 2,  [StatusField]="Pending", 3, True, 999)

Then you can add a sorting or grouping level based on ReportOrder.

Good luck!
Graham Mandeno [Access MVP since 1996]
Distinguished Expert 2017

Commented:
Once you have added the numeric field to sort the file correctly, you will need to add it to your form as the group field.  You can bind it to a control and make the control invisible but it has to be bound to a control.
pdvsaProject finance

Author

Commented:
Ok thank you for the suggestions.  I think I am on my way to a solution.  Not at a computer for awhile though and possibly a few days.  Thank you once again.
pdvsaProject finance

Author

Commented:
thank you.  Works perfectly.  I grouped on ReportOrder switch statement and I did not need to bring that field into the report itself (only grouped by it).  It sorted without having to bind it to another control.

thank you
Distinguished Expert 2017

Commented:
Does it still work after you closed it and reopened it?
Hi Pat

I often base a sorting or grouping level on a field in the RecordSource (or sometimes even an expression) which is not bound to a control on the report.

Cheers,
Graham