Sorting records in a query

R

reeve13

I am having a problem sorting records in a query. I have about 5, 00
entries in a table and when I build my query based on certain fields
the numbers in certain columns are not in order although I use th
sorting feature. ( e.g.) I have 5 columns with numbering data containe
in the columns, and I have it set up to sort in ascending order, whic
it did, but I noticed that the Volume # column sorts the Volume number
differently (see example below):

V. 1
V. 10
V. 11
V. 12
then it goes back to
V. 2
V. 3
V. 4
V. 5
V. 6
V. 7
V. 8
V. 9

It looks like it sorts anything with a 1 first then the rest later.
How do I fix this?

Thanks
 
D

Denis Dougall

It is sorting correctly because those are string values. Numeric values
would be sorted as 1, 2.....10,11,12 etc.

Denis
 
D

Duane Hookom

You could try sort by an expression like:
Left([VolumeNum],2) & Format(Val(Mid([VolumeNum],3)),"0000000")

This makes assumptions regarding your data that might not be valid.

--
Duane Hookom
MS Access MVP
--

Denis Dougall said:
It is sorting correctly because those are string values. Numeric values
would be sorted as 1, 2.....10,11,12 etc.

Denis
Click to expand...
 
Top