Number Sorting Error

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

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sam B." wrote in message
...
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

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" wrote:

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sam B." wrote in message
...
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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sam" wrote in message
news
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" wrote:

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 B." wrote in message
...
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.

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

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

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


"John Spencer" wrote:

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

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

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


"KARL DEWEY" wrote:

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


"John Spencer" wrote:

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

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

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


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

JimS wrote:
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