sorting mixed data types in field

[Mike C]

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

[Bas Cost Budde]

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

[Steve Jorgensen]

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