Sorting numbers in a report

[Cordman]

I've searched the forums and couldn't come with any answers that help in my particular situation. I did find this Here . In the example of RuralGuy (Sort3.zip), I used the SortOrder: FixNum([Field1]) and changed the "Field1" to my needs, but keep getting an error.

Anyway, I have a DB where I track woodworking clamps. Attached you can see that in the report (grouping & sorting) I have it sorted by location, station, color, toolname, & size. Everything works fine...almost. If you look on page 2 of the report towards the top you'll see Wood Clamps and they are sorted 10", 6", & 8", which is incorrect. It should be 6", 8", & 10". Same thing under WS Station 01b. Also, on page 3 under WS Station 06 you'll see the Quik-Grip Clamps are all sorted correctly except the 6" one. Any ideas how I can get these to sort correctly? TIA!

 

[pbaldy]

Well, it's sorting "correctly" because it's a text field, so it sorts alphabetically rather than numerically. Obviously you want numeric sorting though. It would be easy except for the 1/2 items (the Val() function would work, but it disregards the 1/2). Offhand I think you're going to need some sort of function to convert that to a number. Have you considered changing that field to a numeric field and storing 3.5 instead of 3-1/2"?

 

[Cordman]

Have you considered changing that field to a numeric field and storing 3.5 instead of 3-1/2"?

That's what I did and it seems to be working fine. Thanks for your help.

 

[pbaldy]

No problem. That's really the simplest solution. You could always write a little function to display 3.5 as 3 1/2" if you want to view it that way on a report or something.