SOrting on Text field 1

[wdverner]

Hey all,
I have a query that i need to sort- BUT it needs to be sorted on a field ReferenceNo . The ReferenceNo is a text field- it contains letters and numbers.

If I click to sort this Ascending, i get the following sorted output:

1a
10
2a
2b
20a
23a
3

What I need is obviously:
1a
2a
2b
3
20a
23a

So how do I achieve this? Is it possible?

 

[lupins46]

Create a new column in the query which is:
MyVal:Val(ReferenceNo)
Then sort on MyVal followed by ReferenceNo.

 

[wdverner]

Tried that...

See the problem then is then that Val removes the letters from the field and sorts it:

1
2
2
3
20
23

(from the above example...)

 

[lupins46]

Please read my response again.

 

[wdverner]

Lupins
Apologies!

"Then sort on MyVal followed by ReferenceNo."

I sorted by MyVal, problem sorted now! Many thanks, have a star for my inability to read and your correctness as per usual!