Combo Box Sort

[m stroup]

I have a combo box whose source is a table. The list contains room numbers
such as A-1, A-2, A-12. My sort is ascending, but, of course it lists them
A-1,A-2, A-12. Is there a way to fix this? Thanks

 

[John W. Vinson]

I have a combo box whose source is a table. The list contains room numbers
such as A-1, A-2, A-12. My sort is ascending, but, of course it lists them
A-1,A-2, A-12. Is there a way to fix this? Thanks

If (and it's a big if!) the room number is always of the format text - hyphen
- number, and you want it sorted by letter and then by number, you'll need to
split them apart in the Query upon which the combo is based:

SELECT RoomNumber, Left(RoomNumber, InStr([RoomNumber], "-") - 1) AS
RoomLetter, Val(Mid([RoomNumber, "-" + 1)) AS RoomNumberValue
FROM tablename
ORDER BY RoomLetter, RoomNumberValue;

Note that a combo based directly on a table might or might not sort in the
order you expect - a table HAS NO ORDER and will be presented in whatever
order Access finds convenient.

 

[m stroup]

Thanks for the reply John. there are rooms that are formatted as 301 or 204.
Could I check the first digit. If it is not 1-9, then assing room number,
room value as you suggest. If it is 1-9, assign the whole room number to
room number and 0 to room value?

--
Teach me to fish! Thanks for the help.
Pax, M

I have a combo box whose source is a table. The list contains room numbers
such as A-1, A-2, A-12. My sort is ascending, but, of course it lists them
A-1,A-2, A-12. Is there a way to fix this? Thanks

If (and it's a big if!) the room number is always of the format text - hyphen
- number, and you want it sorted by letter and then by number, you'll need to
split them apart in the Query upon which the combo is based:

SELECT RoomNumber, Left(RoomNumber, InStr([RoomNumber], "-") - 1) AS
RoomLetter, Val(Mid([RoomNumber, "-" + 1)) AS RoomNumberValue
FROM tablename
ORDER BY RoomLetter, RoomNumberValue;

Note that a combo based directly on a table might or might not sort in the
order you expect - a table HAS NO ORDER and will be presented in whatever
order Access finds convenient.

 

[John W. Vinson]

Thanks for the reply John. there are rooms that are formatted as 301 or 204.
Could I check the first digit. If it is not 1-9, then assing room number,
room value as you suggest. If it is 1-9, assign the whole room number to
room number and 0 to room value?

You can use the IsNumeric function to detect all-number fields:

SELECT RoomNumber, IIF(IsNumeric([RoomNumber], "0", Left(RoomNumber,
InStr([RoomNumber], "-") - 1)) AS RoomLetter,
IIF(IsNumeric([RoomNumber], [RoomNumber], Val(Mid([RoomNumber, "-" + 1))) AS
RoomNumberValue
FROM tablename
ORDER BY RoomLetter, RoomNumberValue;