convert text to number

[rajesh]

Hi,

I am facing problem of sorting text field, cause it contains
numeric values in place of text.And now i cannot change my datatype
from text to number.
Exp.
I m having following values to my database field
1,2,3,4,11,21,22,31

On sorting i get
1
11
2
21
22
But i want in this way
1
2
3
4
11
21
22
31
plz help me out.

 

[Allen Browne]

If the field contains only numbers, the best solution would be to open the
table in design view, and change the data type. It won't lose the valid
numbers.

If you must store numbers in a Text field, the best solution would be to use
leading zeros, such as 0001, 0012, 0123. That will sort correctly.

If you want Access to sort a Text field as if it were numeric, type this
into the Field row in query design:
Val(Nz([Field1], "0"))
and sort on that. It will get you out of a spot, but it is very ineffient
(no index, and function calls on every row), and Nulls may not sort the way
you intend.

 

[Dale Fye]

As Allen mentioned, if you must store the numeric values as text, then
storing them with leading zeros is the way to go. To update your field to
this format, you could write a query that looks something like:

Update YourTable
Set YourField = RIGHT("00000" & [YourField], 5)

HTH
Dale