Number Sorting Error

[Sam B.]

Hello all,

I'm having an issue with number sorting. I am trying to sort unit numbers
for my condo. When I run the query in ascending order the numbers are not
sorting correctly. Here is a small example of how the numbers are sorting:

1127
117
118
119
1201

Any help? It seems like Access is assuming the three digit numbers have a
zero on the end. In the table the field is set to "text". There are also PH1,
PH2, etc. in the list.

Thank you in advance,
Sam

 

[Allen Browne]

Open your table in design view.
Change the field data type from Text to Number.

For text fields, Access sorts character-by-character, so 1127 sort before
117 since the 3rd character (the 2) is less than the 3rd character of the
next string (the 7.) Use a Number field and it will sort numerically.

 

[Sam]

Thank you Allen, I will try that. What happens though for the unit numbers
that start with PH (for penthouse)? They'll just sort at the end?

Sam

 

[Allen Browne]

You will not be able to put non-numeric characters in a Number field.

 

[John Spencer]

Try sorting using the Val function.
Val(NZ([Unit Numbers],""))

The above expression will sort numerically. Anything that starts with text
will be sorted as if it were zero. So you might want to add another sort on
[Unit Numbers] after the sort by the above expression.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

For PH112 use this --
Val(Trim(NZ(Replace([Unit Numbers], "PH",""),""))))
--
KARL DEWEY
Build a little - Test a little

Try sorting using the Val function.
Val(NZ([Unit Numbers],""))

The above expression will sort numerically. Anything that starts with text
will be sorted as if it were zero. So you might want to add another sort on
[Unit Numbers] after the sort by the above expression.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

You will not be able to put non-numeric characters in a Number field.

 

[JimS]

I had exactly this question I posted in another section of this forum. I
tried your suggestion (which is simple and elegant...), but it doesn't seem
to be valid in the "order by" property of a form. I was able to construct a
query as the Control Source for the form that sorts using val(). My data has
####A, so I simply order by "val(ColumName),ColumnName "

Thanks
--
Jim

For PH112 use this --
Val(Trim(NZ(Replace([Unit Numbers], "PH",""),""))))
--
KARL DEWEY
Build a little - Test a little

Try sorting using the Val function.
Val(NZ([Unit Numbers],""))

The above expression will sort numerically. Anything that starts with text
will be sorted as if it were zero. So you might want to add another sort on
[Unit Numbers] after the sort by the above expression.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

You will not be able to put non-numeric characters in a Number field.

 

[John Spencer]

Val(Replace(Nz([Unit Numbers],""), "PH",""))


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County