By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 1,945 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

sorting mixed data types in field

P: n/a
I have a fairly simple database of registered voters in a county. The
Field for a voters Apartment; RES_APT contains about 85% numeric
("110", "325", "1144", etc etc) and the other 15% is either text and
mixed such as "Floor 2", "A-2", B, "B-1" etc etc etc. (About 40% of
the records are null for this field BTW)
Since I cannot have a numeric field here I have a dilema. As you know
the numbers will sort as text. My problem is obvious, how can I get
the numbers in this text field to sort as numeric without adding
leading zeros. Most of the apt numbers pertain to hi rise buildings
and if I could at least get those numbers to sort as numeric I would
be satisfied since the others are only a small percentage of the
total. Please help if you can. I am not a programmer but can use
Access pretty well. It is important that I sort thes numbers in this
text field the right way!

Mike C
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Mike C wrote:
Since I cannot have a numeric field here I have a dilema. As you know
the numbers will sort as text. My problem is obvious, how can I get
the numbers in this text field to sort as numeric without adding
leading zeros.


Append leading spaces?
--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

Nov 12 '05 #2

P: n/a
On 14 Feb 2004 13:51:16 -0800, mi************@earthlink.net (Mike C) wrote:
I have a fairly simple database of registered voters in a county. The
Field for a voters Apartment; RES_APT contains about 85% numeric
("110", "325", "1144", etc etc) and the other 15% is either text and
mixed such as "Floor 2", "A-2", B, "B-1" etc etc etc. (About 40% of
the records are null for this field BTW)
Since I cannot have a numeric field here I have a dilema. As you know
the numbers will sort as text. My problem is obvious, how can I get
the numbers in this text field to sort as numeric without adding
leading zeros. Most of the apt numbers pertain to hi rise buildings
and if I could at least get those numbers to sort as numeric I would
be satisfied since the others are only a small percentage of the
total. Please help if you can. I am not a programmer but can use
Access pretty well. It is important that I sort thes numbers in this
text field the right way!

Mike C


Let's say, you're field is called Foo...

.... ORDER BY IIf(IsNumeric(Foo), CDbl(Foo), CDbl(0)) Asc, Foo Asc
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.