Sorting a text box via a query

[Kay Davies]

Hi
I recently had some very helpful advice on how to sort my text data using a
query. The suggestions were to use Val(MyField) as a sort (I am replacing the
MyField with my field name, I assume that is correct?). I was hoping someone
could help further as to where to put this as wherever I put it in the query
I get an error of some kind. If I put it in the 'sort' row then it just tells
me I am not selecting from the list. Any help with this would be gratefully
received.
Many thanks

 

[Ken Snell \(MVP\)]

Open your query in design view.

In the first empty column on the grid, put this in the Field: box:
SortingField: Val(NameOfYourFieldGoesHere)

Uncheck the Show checkbox for this column.

Select Ascending for the Sort option for this column.

 

[Kay Davies]

Hi Thanks so much for this. Unfortunately it is still not sorting my text
exactly right. The box contains a reference number which is a mixture of
numbers and letters. When it reached 999 and then went on to 1000 etc it
won't put the 1000 next to the 999 but with the 10s (I hope this makes sense)
To give you an example of the number
999-08-B and 1015-08-D

many thanks

 

[Douglas J. Steele]

Assuming you used the Val function like Ken suggested, it should treat 999
and 1000 properly.

 

[Ken Snell \(MVP\)]

The Val function returns a number, which will sort 999 before 1000. Make
sure that no other fields in the query are being sorted, especially not the
original field.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Pete D.]

This is because you have combinations of types of values both text and
numbers See this site
http://office.microsoft.com/en-us/access/CH100645761033.aspx
and read the article on Sort IP Address. Instead of inspecting the string
for a period as in the example use the dash.