Question Mixed text and number sorting.

[AnthonyJBaker]

G'day All.

I am constructing a database to store the details of my collectible cards.

One of the fields stores the cards Collector Info, which is of the form L 000 in a text field (due to the leading text character) and the problem occurs when this is sorted, due to the dictionary sort that Access applies because the field is a text field. I.e. you get:

F 14
F 15
F 154
F 16

whereas I want to output in the order

F 14
F 15
F 16
F 154

Any advice would be appreciated; I was thinking that I might have to have two fields, one for the Text and one for the Number but that feels a bit inelegant.

Regards

Anthony

 

[gemma-the-husky]

you either need 2 fields - or with a single field - you need to make all the values the same length

so instead of F 1 - you need F 001

then the dictionary sort and numeric sort are the same