By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,454 Members | 2,629 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,454 IT Pros & Developers. It's quick & easy.

Report SORTING and GROUPING problem

100+
P: 135
Hello,

I have a table with categories:
Metal
Metal Added
Foam
Wire Harnessm etc.

And i need to keep THIS specific order of the categories for the reports, however i need to sort and group the reports including this field and so the ONLY options available are Ascending and Descending. I can achieve this by numbering every category (0,1,2...9,91, etc) but its confusing for the user.

Is there ANY way (code, property...trick) to do it???

thanks,
Gilberto
Oct 17 '07 #1
Share this Question
Share on Google+
3 Replies


puppydogbuddy
Expert 100+
P: 1,923
Hello,

I have a table with categories:
Metal
Metal Added
Foam
Wire Harnessm etc.

And i need to keep THIS specific order of the categories for the reports, however i need to sort and group the reports including this field and so the ONLY options available are Ascending and Descending. I can achieve this by numbering every category (0,1,2...9,91, etc) but its confusing for the user.

Is there ANY way (code, property...trick) to do it???

thanks,
Gilberto
if you don't have a lot of categories, you can use the hidden textbox trick.
1. Place a textbox(named txtCatSort) next to the textbox for Category, set its visible property to No after it is working.
2. for the control source, put the following:
= IIf([Category] = "Metal",1,IIf([Category] = "Metal Added", 2......and so on<<<you are allowed 8 levels of nested ifs
3. go to Grouping/Sorting window, add txtCatSorto next available line, then drag to first line above category. use for sort only-not for grouping.
4. test it out.

if you have a lot of categories, then I would the number code as a prefix in the Category name like 1_Metal and sort and group by the prefix character.
Oct 17 '07 #2

100+
P: 135
if you don't have a lot of categories, you can use the hidden textbox trick.
1. Place a textbox(named txtCatSort) next to the textbox for Category, set its visible property to No after it is working.
2. for the control source, put the following:
= IIf([Category] = "Metal",1,IIf([Category] = "Metal Added", 2......and so on<<<you are allowed 8 levels of nested ifs
3. go to Grouping/Sorting window, add txtCatSorto next available line, then drag to first line above category. use for sort only-not for grouping.
4. test it out.

if you have a lot of categories, then I would the number code as a prefix in the Category name like 1_Metal and sort and group by the prefix character.
Thanks for the reply.

I have 12 categories so option 1 doesnt work.
Option 2 (grouping by prefix) sorts and groups the categories exactly the same as if i didnt use the "group and sort by prefix...", displaying categories like:
1_Metal
2_Metal Added
3_Plastics...

I need to eliminate the # at the beginning.

Any other idea???
Oct 17 '07 #3

puppydogbuddy
Expert 100+
P: 1,923
Thanks for the reply.

I have 12 categories so option 1 doesnt work.
Option 2 (grouping by prefix) sorts and groups the categories exactly the same as if i didnt use the "group and sort by prefix...", displaying categories like:
1_Metal
2_Metal Added
3_Plastics...

I need to eliminate the # at the beginning.

Any other idea???
Just an idea...have never tried it. Create a hidden combobox (cboCatSort) with 2 columns, Category ; SortOrder

In sorting grouping tool put the following expression on first line for sorting only:
= cboCatSort.Column(1)
Oct 17 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.