ORDER BY

[Ingman]

Hi all,

I have a text field in which about 90% of the records actually are numeric,
I guess this is a classical dilemma. I want to sort the forms/lists in a
"nummeric" behavior, i.e 1,2,3... etc not as string sort which gives 1,10,11
,2 etc... I have read a couple of posts and used the LPad(Left padding)
method, however this method has really bad performance, takes forever when
the user pushes for example a combobox where rowsource uses LPad. In oracle
one can use function based index, are there any equivilant method in access?
If anyone could point me to a solution, i would really appreciate it.

Best Regards,

Ingman

 

[John Spencer]

You could try the following. It might be a bit faster.

ORDER BY IIF(IsNumeric([SomeField]),Val([SomeField]),[SomeField])

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