Ordering problem

[RipperT]

I have a table with room numbers and bunks. Most have just two bunks
referred to as U for upper and L for lower. It's important that each room
show the upper bunk first, lower bunk second. But then there might be a room
with bunk A, B, C and D. Is there a way to order a query/report so they show
up ordered thus:

Room, bunk

1U
1L
2U
2L
3A
3B
3C
3D
4U
4L
5U
5L

etc...

 

[John Spencer]

Easiest way is to build a table with two columns. Column 1 is the bunk
value and column two is the order you want the the sort.

U 1
L 2
A 10
B 20
C 30
D 40

Now add that table to the query and sort on the second column

OR you can use a calculated column and sort on the calculation

Instr(1,[Bunk],"ULABCDEF")

That should return the position of the letter in the string and you can
sort by that position.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================