How to sort text column by number sorting in Access?

I have a column with address numbers (1, 1a, 3, 12b, ...etc). I need t开发者_高级运维o sort them as numbers (result as above), not text (such as 1, 1a, 12b, 3, ...etc).

What would be the sorting clause for this issue?

Help appreciated.

Try this:

Val(iif( /columnName/ IS NULL, 0, /columnName/ )) /order/