Report Sorting

[Guest]

I have a report that has the following information:

Client
Products Type Price
Client
Products Type Price .....

I want to products to appear sorted by type which I have been able to do
using the report wizard. However, the types appear alphabetically and I want
them to appear in a differ order. Is there a way to sort them manually?

 

[Rick B]

What order do you want them in? If there is a field you can use to sort,
select that. If you have an arbitrary order, then you need to add a "sort"
field to your table and enter the order there.

 

[Guest]

It is an arbitrart order. How would I sort then?

 

[fredg]

It is an arbitrart order. How would I sort then?

Let's say your types are
"A", "B", "C","D" and you wish to sort them in the report in an
arbitrary "B","D","C","A" order.

In the query that is the report's record source add a new column.
SortThis:IIf([Type]="A",4,IIf([Type] = "B",1,IIf(Type] = "C",3,2)))

Then in the report, use the Report's Sorting and Grouping dialog (View
+ Sorting and Grouping) to sort using this SortThis field.

 

[Duane Hookom]

As Rick B stated, if you want a specific order, you must have a field in
your products table (or other) that identifies the specific order.
For instance, if you have a table of colors that you want to sort

Color
======
Red
Green
White

You have to add a field
Color Seq
===== ========
Red 1
Green 2
White 3

 

[John Spencer]

Do you have a types table? If so, add a field called SpecialSortOrder to
it. Populate that field with values that will sort in the order you want
and then include that in the query for the report. You can then sort by the
field (you don't have to display the value on the report).

I usually use a number field (type double) for Special Sort Order. That
makes it easy to insert something in the middle of the sort order if someone
decides to change the order or add more items. I can just use 1.5 for the
new item to get it to sort between the items numbered 1 and 2. If I need
another item added between 1 and 1.5, then its special sort order value is
1.25

 

[Guest]

I tried your code but it says that there is a syntax error...maybe with the (
)?

It is an arbitrart order. How would I sort then?

Let's say your types are
"A", "B", "C","D" and you wish to sort them in the report in an
arbitrary "B","D","C","A" order.

In the query that is the report's record source add a new column.
SortThis:IIf([Type]="A",4,IIf([Type] = "B",1,IIf(Type] = "C",3,2)))

Then in the report, use the Report's Sorting and Grouping dialog (View
+ Sorting and Grouping) to sort using this SortThis field.

 

[Guest]

Got it-THANKS!

It is an arbitrart order. How would I sort then?

Let's say your types are
"A", "B", "C","D" and you wish to sort them in the report in an
arbitrary "B","D","C","A" order.

In the query that is the report's record source add a new column.
SortThis:IIf([Type]="A",4,IIf([Type] = "B",1,IIf(Type] = "C",3,2)))

Then in the report, use the Report's Sorting and Grouping dialog (View
+ Sorting and Grouping) to sort using this SortThis field.

 

[Guest]

Would the SpecialSortOrder values have to appear on the report? My query
sorts by three fields, the first of which doesn't appear on the report, and I
don't know how to get the report to use the first field.

Thanks!

 

[John Spencer]

You can put it on the report and set the control's visible property to NO;
however, as long as you include it in the Record Source you don't have to
use it in a control on the report.

So the answer is it must be in the reports record source, but it doesn't
have to be in the report.

 

[Guest]

Great, thanks for the quick reply!
--
Ellen

You can put it on the report and set the control's visible property to NO;
however, as long as you include it in the Record Source you don't have to
use it in a control on the report.

So the answer is it must be in the reports record source, but it doesn't
have to be in the report.