Question Filter by Form....Search & Sort

[uzymedphys]

Hi

I have developed an Access 2003 database that uses the built-in search and sort function using 'filter by form'.

Problem: I have 'text' fields such as:

2.0 x 3.4
4.3 x 9.8
6.7 x 6.6
9.9 x 9.9

The above get sorted very nicely - but I have realised that the 'sort' function looks at the first character and then sorts the text fields. This produces the following result:

10.3 x 2.2
10.0 x 3.4
2.0 x 3.4
4.3 x 9.8
6.7 x 6.6
9.9 x 9.9

i.e. text fields > 9.9 end up sorted incorrectly.

What I would like is the sorting whether ascending or descending is completed correctly i.e. 1,2,3,4....9,10,11,12. So, what I really want is for it to sort it using two characters instead of one. One way I can resolve this is by splitting the text field into two fields but this means I need to do a lot of redesigning! So I really don't prefer this route. Can I customise the built-in sort facility without using advanced sorting functions? Or there an elegant solution?

I would be very grateful for any solutions!

Many thanks

Usman

 

[neileg]

Add a calculated field to the dataset using Val() this will return the characters at the start of the string that can be interpreted as a number. Sort on that calculated field.