Sorting records in a query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

reeve13 - 09 Nov 2005 15:14 GMT

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

--
reeve1

Reply to this Message

Denis Dougall - 09 Nov 2005 16:05 GMT

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

Denis

> I am having a problem sorting records in a query. I have about 5, 000
> entries in a table and when I build my query based on certain fields,
[quoted text clipped - 22 lines]
>
> Thanks.

Reply to this Message

Duane Hookom - 09 Nov 2005 17:11 GMT

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.

Signature

Duane Hookom
MS Access MVP
--

> It is sorting correctly because those are string values. Numeric values
> would be sorted as 1, 2.....10,11,12 etc.
[quoted text clipped - 27 lines]
>>
>> Thanks.

Reply to this Message