Strict Numeric Order

[davedynamic]

In trying to make a query of sizes I have found a disconcerting flaw I do not know how to address.

My stock lines include sizes from 15 mm to 700 mm in 5 mm increments. When I sort these ascending and group by. I end up with 80mm being greater than 700 mm.

How do you sort strictly numerically?

 

[shacket]

You need to have that information in a number field, not a text field. A text field sorts by the first character, meaning 700 comes before 80 (because 7 comes before 8). A numeric field looks at the actual number and would sort it appropriately.

 

[R. Hicks]

Add a field expression to the query.

MySort: Val([YourSizeFieldName])

(change "YourSizeFieldName" to the actual name of your field that contains the size data)

Then set the sorting order by this field expression.

HTH
RDH

[This message has been edited by R. Hicks (edited 09-28-2001).]

 

[Pat Hartman]

You might consider changing the table design so that you use two fields - Size and UnitOfMeasure. Storing multiple pieces of information in a single column is ALWAYS wrong.

Rick's suggestion to use the Val() function will work for you as long as the numeric portion of the mushed field is first.

 

[R. Hicks]

I agree with Pat and I love that vivid description of the "mushed field".

RDH