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.

 

[Joseph Meehan]

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.

You can't. Space is a character, Space comes before zerro. Sorting is
done on the first character, then likes are sorted by the next character.
Numbers letters and spaces are all characters in a text field.

Well you could but you would need to do some serious code to separate
out the number portion from the text portion.

I suggest breaking up the LocNo into two fields. One with the numeric
and one with the text. You will still need to do some formatting and you
can re-combine then in queries, forms or reports.

Remember that if you leave them at text "1[Space]" will sort before
"0" If you convert them to numbers then 1 will sort before 10 and you can
change the format later to make it look like you want.

 

[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.