Report Grouping and setting a sort order

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?
pdvsaProject financeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

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

thank you
GrahamMandenoCommented:
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]

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

PatHartmanCommented:
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 financeAuthor 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 financeAuthor 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
PatHartmanCommented:
Does it still work after you closed it and reopened it?
GrahamMandenoCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.