Efficient way to sort Text numbers in numeric order (1 Viewer)

[Rx_]

What is the most efficient way to sort text numbers into numeric sort order? An example is attached.

Two columns - WName and WNumber.
These are formal name assignments set by a regulatulated agency. So we have 1, 11, 111 (these can not be 001).
See attachment, note how Wnumber sorts. The 9 comes after 71. If there was a 666, it would sort between 3 ad 67 in this example.

This is an Access DB, but the linked table will soon be on SQL 2008.

A contrived field of My WNumSort: Cint([WNumber])
could be created with the sort assending assigned to it.

Would that be the most efficient way to control this sort?
For the SQL Server - this could become a view so the sorting takes place on SQL Server.

 

[pbaldy]

It sounds like the field is text, so that would be one way to get the sort. In T-SQL you'd use CAST.

 

[Rx_]

Was excited to find this solution that works super effeciently in Access 2010. Wanted to share my findings. Doh! Forgot about the VAL function.

The problem is that my text field may or may not have padded, leading zeros or an empty string. (e.g. "2", "02", "0002", "")
Custom Functions trying to cover every situation became more complex for sorting and slower.

After reviewing literally dozens of solutions posted on the Internet, found what I feel is the fastest and best solution at Microsoft:
http://office.microsoft.com/en-us/a...alues-stored-in-a-text-field-HA010062652.aspx

Expr1: IIf([Fieldname] Is Null, 0, Val([Fieldname]))

As I am in the middle of Access 2010 to SQL 2008 conversion, the response above to my question is also very appreciated.

Please "thank" the volunteers who make this site so valueable.