How to sort a column of numbers properly?

[Spruce4U]

This may sound like a very basic question, but how to make Access sort properly a column of numbers?

For example, instead of logically sorting my records like the following:

1
2
3
4
5
6
7
8
9
10
11
12

Access sorts them like this:

1
10
11
12
2
3
4
5
6
7
8
9

 

[belovedcej]

Your field is probably text instead of integer.

If it is integer, it will sort properly.

If it is text, you will need to have leading zeros.

 

[Spruce4U]

It is a calculated field in a query and I have put the format 'general number' to it.

Do you have any more clues?

 

[belovedcej]

Hmm - I'm a little stumped on that. I looked around for some clues - maybe try casting it as a long?

CLng(nameOfField)

Unless you want decimal points - then CDbl(nameOfField)

I don't tend to use access calculated fields, so I'm guessing, too.

 

[PHV]

Have a look at the Val function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Spruce4U]

PHV,

Thanks but actually, the calculated field uses the Val function already.


belovedcej, thanks for the comments.

 

[PHV]

Any chance you coukd post your actual SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Spruce4U]

PHV,

Here is the SQL code for 2 calculated fields, the second one (col) giving the sorting problems.

IIf([pos_sur_plaque]<>"",Left([pos_sur_plaque],1),"") AS row

IIf([row]<>"",Val(Replace([pos_sur_plaque],[row],"")),"") AS col

where:

[pos_sur_plaque] is an alphanumeric chain in the style of "A01", "A02", "G12" etc.

The "row" calculation gets the first character (here "A", "G", etc.)

The "col" calculation converts the rest ([pos_sur_plaque] with "row" removed) to a number format using the 'Val' function.

 

[PHV]

ORDER BY Val(Mid([pos_sur_plaque],2))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886