sorting numbers in text format in query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here is a sample list:

500-21
500-6
500-19

In my query, even if I parse everything to the right of the dash, do
FormatNumber() on the expression, and try to sort on that column, I still get:

500-19
500-21
500-6

You know what I want to see. Requiring users to enter leading zeros is out
of the question.

You've helped me in the past,

Thanks,

RLiss
 
Dear RLiss:

How about splitting the text at the hyphen (as two separate columns), then
converting both parts to numeric values, then sorting on those numeric
values? Sorting numeric is a key to what you want.

Tom Ellison
Microsoft Access MVP
 
Tom, I am already doing that. I am using the formatnumber() function to
convert it to a numeric. In oracle I would use to_num() function. Perhaps
is there another way to convert to numeric.
 
Dear RLiss:

Generally, and function that "formats" converts its result to text. For
your purpose, the CLng() function would be a good choice.

Tom Ellison
Microsoft Access MVP
 
Back
Top