Problem with sorting numbers

H

Hugo

Hi,

I've got a table in Access with a number structure like this

1.1 (row 1)
1.2 (row 2)
2.1 (row 3)

The problem is, when I introduce 10.1, it comes at the top because it's a
text field because of the dot.
Is there any solution to sort this like it would with a normal number field,
so that the 10.1 comes at the bottom?

I tried using a special sorting column with just 1, 2, 3 etc in it so that
it would sort correctly, but when I have to insert a new record there is a
problem.
Let's say my column goes up to 14.2 and it's row number 37.
Now when I have to add 9.5, it will come at the bottom, on row 38 with
number 38 in the sorting column. Of course this is incorrect.

Hope it's a bit clear what I mean... Now what can I do?

Thanks
Hugo
 
R

Roger Carlson

In a query you will create an additional field based on your "text" number
and sort on that. Assuming your field is called "TextNumber", it would look
something like this:

SELECT TextNumber
FROM MyTable
ORDER BY CSng([TextNumber]);

CSng converts the text number into a real number just for the purposes of
sorting. This will work in a query , form, and report, but not directly in
the table. But then you shouldn't give your users access to the actual
tables anyway.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Top