Sorting text field like a number field

[Beth]

I have a text field that stores numbers that may occassionally have letters
after them. I need to be able to sort this field as if it is a number field
even though it holds text. Unfortunately, that is not something I have an
option to change, but I have to find a way to work with it.
The problem comes with the numbers 1-9 falling in the wrong place with the
text sort.
Does anyone have a suggestion on how to correct that in the sorting? The
only thing I came up with was very messy. That was to use nested if
statements to handle 1-9.

I am open to suggestions.

Thanks,
Beth

 

[Allen Browne]

Type this expression into the Field row in query design:
Val(Nz([Field1],"0"))

Choose Ascending in the Sorting row under this field.

Val() ignores trailing non-numeric charactrers.

 

[fredg]

I have a text field that stores numbers that may occassionally have letters
after them. I need to be able to sort this field as if it is a number field
even though it holds text. Unfortunately, that is not something I have an
option to change, but I have to find a way to work with it.
The problem comes with the numbers 1-9 falling in the wrong place with the
text sort.
Does anyone have a suggestion on how to correct that in the sorting? The
only thing I came up with was very messy. That was to use nested if
statements to handle 1-9.

I am open to suggestions.

Thanks,
Beth

If the data is like:
8123PLU
23XZ
765KJM

Create a query that will be used as the form's record source.
Add a new column.

SortThis:Val([Fieldname])

Sort by this field.