Order by a character field

[hin]

Hi

I have the a simple table:

Location
-----------------
ID Autonumber
LocNo Character
Desc Memo

with the value:
ID LocNo Desc
1 1 abc
2 2 xyz
3 10 mno
4 3 ...
.....
10 9 ...

When I order the table by LocNo, I get in the following order:
LocNo Desc
1 abc
10 mno
2 xyz
3 ..
...
9

How can I make it order by 1, 2, 3...9, 10?

Thanks!
H.

 

[Ken Snell]

Add a calculated field to the query:
ValueLocNo: Val([LocNo])

Uncheck the "Show" checkbox. Set its order to Ascending.

 

[Ernie]

What Ken wrote won't work if you have any letters in with
your numbers in this field (e.g. 1A)

What you could do in this case would be to right justify
your LocNo and fill in with leading zeroes. This way 1
becomes 01, 2 = 02, etc. Fill in with only as many zeroes
as absolutely necessary (i.e. if the longest LocNo is 1000
then the lowest number in here would be 0001 not
000000000001.

You can always strip out the leading zeroes with a format
in forms and reports.

-----Original Message-----
Add a calculated field to the query:
ValueLocNo: Val([LocNo])

Uncheck the "Show" checkbox. Set its order to Ascending.

--

Ken Snell
<MS ACCESS MVP>

Hi

I have the a simple table:

Location
-----------------
ID Autonumber
LocNo Character
Desc Memo

with the value:
ID LocNo Desc
1 1 abc
2 2 xyz
3 10 mno
4 3 ...
....
10 9 ...

When I order the table by LocNo, I get in the following order:
LocNo Desc
1 abc
10 mno
2 xyz
3 ..
..
9

How can I make it order by 1, 2, 3...9, 10?

Thanks!
H.

.

 

[Ken Snell]

Excuse me, but the original post shows that LocNo is a text-formatted field
that contains only numeric characters. That is why I proposed the solution
that I did.

If the poster had had values such as 1A, then the change that I would
suggest is to add the LocNo field a second time to the query, uncheck the
Show checkbox, and set its sort order to Ascending. Thus, two sort fields
would be used -- the first one, using your example, would return
Val(1A) = 1
while the second one then sorts on any trailing letters.
--

Ken Snell
<MS ACCESS MVP>


It will work to sort the numbers. And "Ernie"

What Ken wrote won't work if you have any letters in with
your numbers in this field (e.g. 1A)

What you could do in this case would be to right justify
your LocNo and fill in with leading zeroes. This way 1
becomes 01, 2 = 02, etc. Fill in with only as many zeroes
as absolutely necessary (i.e. if the longest LocNo is 1000
then the lowest number in here would be 0001 not
000000000001.

You can always strip out the leading zeroes with a format
in forms and reports.

-----Original Message-----
Add a calculated field to the query:
ValueLocNo: Val([LocNo])

Uncheck the "Show" checkbox. Set its order to Ascending.

--

Ken Snell
<MS ACCESS MVP>

Hi

I have the a simple table:

Location
-----------------
ID Autonumber
LocNo Character
Desc Memo

with the value:
ID LocNo Desc
1 1 abc
2 2 xyz
3 10 mno
4 3 ...
....
10 9 ...

When I order the table by LocNo, I get in the following order:
LocNo Desc
1 abc
10 mno
2 xyz
3 ..
..
9

How can I make it order by 1, 2, 3...9, 10?

Thanks!
H.

.

 

[hin]

Thanks Ken.

If I have

1A
1C
1B

how can I sort this in order 1A, 1B, and 1C, since Val on them = 1


Thanks again

 

[Ken Snell]

See my post to which you replied. You add a second sorting field as I've
indicated.