Ordering by textfield

[Marco Alting]

Hi,

I have the following sequential textfields which are required to "number" my
records, but when I go above 10 it puts it in between when querying the
records. Like this:

98.1
98.10
98.2
98.3
98.4
98.5
98.6
98.7
98.8
98.9

While it should be like this:

98.1
98.2
98.3
98.4
98.5
98.6
98.7
98.8
98.9
98.10

Can anyone tell me what I can do so that the ordering is correct?

 

[Tom Ellison]

Dear Marco:

This is a common misconception. It is known as "Dewey Decimal
Disease" or just DDD for short.

If you arrange these alphabetically, what is the correct order?

Marco.Alting
Marco.AltingA
Marco.AltingAB
Marco.AltingB

If you said they are already in order, you are correct. Well, digits
sort alphabetically the same way! 1 comes before 2 no matter whether
it is followed by a 0 or not.

There is no facility of which I'm familiar that will sort Dewey
Decimal strings as they are. However, there is a way to accomplish
this.

First, you must know the maximum number of digits that can be used
between the decimal places (and there is often more than 1 decimal
place, although this works for just one as well as for more.

You need a function that creates a new string that is zero filled left
having the selected number of digits for each section of the number.
You can drop the decimal points at this time.

If you select 3 as the number of digits in each section, your list
would look like this:

098001
098002
098003
098004
098005
098006
098007
098008
098009
098010

This will sort as you wish. So, you can sort by the converted value
strings and get the desired order.

I'm not going to stay up later tonight to write this for you, but let
me know and I'll try to fit it in tomorrow if you need it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Marco Alting]

Thanks a lot Tom for clearing that up, I'll try and fix it myself, and if
I'm not able to fix it I'll post another request tomorrow (By the way it's
8:45 in the morning over here).

Thanks again,
Marco

 

[Kelvin]

Another option is to convert the text into numbers and sort by this
temporary conversion. If you are doing this through a query create 2
fields, 1 for the integer part, and another for the decimal part.

TempInt: CInt([Number])
' This drops out the decimal part.

TempDec: CInt(right([Number],len([number])-Instr([Number],".")))
' This converts everything after the decimal into the integer equivalent.
(.1 becomes 1, .10 becomes 10)

Then sort the 2 in ascending order.

98 1
98 2
..
98 9
98 10

Kelvin