Sort

[George Schneider]

I have a report that displays various inventory values at our facilty. Each product is listed including the formaula SKU. I've noticed an error in the sort order. At some points its corect at at others it incorrect. For example 12811055, 2811005, 12811 is the sort order in some cases. I would think the sort order should be 12811,12811005, 128055. In some cases this is true not in all though. The other thing is I have a column called comments. I want to be able to dispaly a product group if any of the products in the grouping the comments field is <MIN. Right now I only pull products where comments <MIN. I want to dispaly the entire product group if any of the products are less <MIN. Any help would be greatly appreciated.

 

[Wayne Morgan]

You have typos in the numbers you mention (they change in each set) so it is hard to tell
what you are after. However, this type of error usually occurs from sorting the numbers as
text instead of in numeric order. What is the field's data type?

--
Wayne Morgan
Microsoft Access MVP

I have a report that displays various inventory values at our facilty. Each product is

listed including the formaula SKU. I've noticed an error in the sort order. At some
points its corect at at others it incorrect. For example 12811055, 2811005, 12811 is the
sort order in some cases. I would think the sort order should be 12811,12811005, 128055.
In some cases this is true not in all though. The other thing is I have a column called
comments. I want to be able to dispaly a product group if any of the products in the
grouping the comments field is <MIN. Right now I only pull products where comments <MIN.
I want to dispaly the entire product group if any of the products are less <MIN. Any help
would be greatly appreciated.

 

[George Schneider]

The field is a text field beacuse we have SKU's that start with ALpha characters as well as numberic ones.

 

[Wayne Morgan]

Try creating a calculated field for sorting purposes only, it doesn't have to be displayed
in the output, and sort on this field.

SortOnMe: IIf(IsNumeric([Table1].[Field1]), Eval([Table1].[Field1]), 99999)

Use a high enough number for the 99999 that text items will be placed after numeric ones
or use a low enough number for 99999 that text items will be sorted before numeric ones,
your choice. The "99999" will have to be larger/smaller than the largest/smallest numeric
only value.

--
Wayne Morgan
Microsoft Access MVP

The field is a text field beacuse we have SKU's that start with ALpha characters as well

as numberic ones.