I have a table with around 18,000 records in it. One of the fields is
a Text field, but 85% of the things in that field is a number (The
other 15% is a number with a text suffix).

I was wondering if there was an easy way to Sort this field as if it
were a number (because, when it sorts as text it looks like this...

1
11
12
14
2
21
24
3
35


So far, the only thing I came up with was to make a query with this
expression...

TempSection: CDbl([Section])

But that either produces a #Error for those rows where the Section has
a text character in there or it won't even open the query in datasheet
view because of Data type mismatch in criteria expression.

What can I do?

Can I create an iif statement to catch that error? or Something?

Thanks!
--
Nathan

Try this:
sc:iif(isnumeric(x!y),cdbl(x!y),0)

where X is table and Y is field.

Sort on this but display y.

This will place all fields containing text at the top and then sort
numbers. If you want all text fields at the bottom then change 0 to
99999999.

If you really need to sort the text contianing fields as well you will
need a funtion to extract the numeric part and use that as the
arguement to cdbl. You wouldn't need the iif function then.

Good Luck
Ira Solomon

On 5 Oct 2003 16:31:25 -0700, ngiven (AT) hotmail (DOT) com (Nathan Given) wrote:

in the query pane where the fieldname is overtype with: cint(
fieldname ). This will convert the field into a interger, so when
sorted, it appears in the correct order.

if there are also letter charactors stored in that field in a normal
SQL environment you can use a case statement: something like

case when isnumeric(fieldname) = true then cint(fieldname) else
fieldname end

But unfortuantely access sql, insn't ansi standard so its not that
good.

I hope this helps....
Philippa

ngiven (AT) hotmail (DOT) com (Nathan Given) wrote in message news:<f749d926.0310051531.a1f1a3d (AT) posting (DOT) google.com>...

Should be a simple matter of ORDER BY val(strField) if I'm
understanding your problem correctly... but it's late.

to test this create a table (tblStrNum) with 2 fields
(str:text,num:number)
open the table and enter 1,22text,100 into str. now enter 1,22,100
into num.

create a query (qrySortStrNum):

Select str,num
FROM tblStrNum
ORDER BY str;

str num
1 1
100 100
22text 22

this represents your current situation. now modify the sql to read:

Select str,num
FROM tblStrNum
ORDER BY val(str);

result:

str num
1 1
22text 22
100 100

and thereyago :-)

-John

ngiven (AT) hotmail (DOT) com (Nathan Given) wrote in message news:<f749d926.0310051531.a1f1a3d (AT) posting (DOT) google.com>...

I did it this way. In my case, I had street numbers [STREET_NUMBER]
entered as text. None were > 999, but some had non-numerical junk, or
nulls.
This method left records with unusual content unmodified, and useful
results that could be sorted nicely. You could expand the code to cover
longer strings.

STREET_NUM: IIf(Len([STREET_NUMBER])=1," " &
STREET_NUMBER],IIf(Len([STREET_NUMBER])=2," " &
[STREET_NUMBER],[STREET_NUMBER]))

John wrote: