Is there a way to sort by a text field but have it sort numerically.

I have an application that I have a text field but some record sets are only
numbers. As you know the "order by" does not sort them the way it would if
it was a numeric field is there any way around this?

    Thanks for any help!!!

Depending on what you are using but you can try VAL() in SQL

Order by val(field_name)




Show quoteHide quote

Load it into an array as numbers using the val(str) function - and sort the
array.
But if Grant's solution works, I like his idea better.

Thanks very much ALL this really helped!!!


Show quoteHide quote

Here is a solution that I think will sort data, whether the sort field
contains numbers or text:

SELECT * from tblBins order by IIf(IsNumeric(BinNum),0,BinNum),Val(BinNum)
ASC

The actual VB code is :

    SQL = "SELECT * from " & LkTable
    SQL = SQL & " order by "
    SQL = SQL & "IIf(IsNumeric(" & LkSortField & "),0,"
    SQL = SQL & LkSortField & "),"
    SQL = SQL & "Val(" & LkSortField & ")"
    SQL = SQL & " " & LkSortUpDown
    Set RS = DB.OpenRecordset(SQL)

add new line to task body
How to open file for output but not overwrite?
All About XP ..
Do I just not get it?
How to create an array of images
Eliminate duplication in an array
Visual Basic and Word
.dat retrieval and display
can't get form to start up when activeX exe starts
How to protect software?