Number Sorting Error

  • Thread starter Thread starter Sam B.
  • Start date Start date
S

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
 
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.
 
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
 
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
 
For PH112 use this --
Val(Trim(NZ(Replace([Unit Numbers], "PH",""),""))))
--
KARL DEWEY
Build a little - Test a little


John Spencer said:
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 said:
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 said:
For PH112 use this --
Val(Trim(NZ(Replace([Unit Numbers], "PH",""),""))))
--
KARL DEWEY
Build a little - Test a little


John Spencer said:
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 said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top