Acsending order

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query where I need to sort the data into accsending order. The
column contains several entries of the following:
BH 3, BH 4, BH 5, BH 6, BH7, BH8, BH9, BH 10 and BH 11.
I want to show all the BH 3 entries together, all the BH 4 entreis together,
etc. If I sort accsending although it groups all the entries together I end
up with the column in the following order:
BH 10, BH 11, BH 3, BH 4, BH 5, BH 6, BH7, BH8 and BH9
How do I get it to show the BH 3 entries first and BH 10 and BH 11 last? I
need it in the correct order otherwise it affects the order when I create a
report graph i.e. the legend shows the series in the wrong order.

Thank you
 
It is possible to parse the field using Left(), Mid(), IsNumeric(), Len()
and so on, but why bother? It would be much easier to use 2 fields: one for
the alpha prefix, and the other for the number.

Apart from the fact that this matches the design goal of keeping all fields
atomic (i.e. only store 1 thing in any field), it will be much more
efficient. You can create a unique index on the pair of fields if the
combination must be unique.
 
Hi,


If there is always two letters (and, or, a third character as a space):


ORDER BY VAL( MID(MyField, 3) )


with MID that select the substring and VAL that tries to change the (sub)
string as a number




Hoping it may help,
Vanderghast, Access MVP
 
You think of numbers after the Text "BH " but to Access / Jet, they are just
Text strings (with some digit characters) and Access / JET compare String
character by character from the left and "3" is after "1" regardless of the
characters after. Hence "BH 3" is after "BH 1" regardless of the remaining
characters.

If your entries always contain the "BH " and then the digits, you can use a
calculated Field in your Query:

SortNo: CInt( Mid([YourField], 4) )

and then sort ascendingly accoring to this SortNo.
 
Thank you - being a Access semi-novice I found Van solution understandable
and its works!

Thank you for all your suggestions!

Van T. Dinh said:
You think of numbers after the Text "BH " but to Access / Jet, they are just
Text strings (with some digit characters) and Access / JET compare String
character by character from the left and "3" is after "1" regardless of the
characters after. Hence "BH 3" is after "BH 1" regardless of the remaining
characters.

If your entries always contain the "BH " and then the digits, you can use a
calculated Field in your Query:

SortNo: CInt( Mid([YourField], 4) )

and then sort ascendingly accoring to this SortNo.

--
HTH
Van T. Dinh
MVP (Access)


KIM said:
I have a query where I need to sort the data into accsending order. The
column contains several entries of the following:
BH 3, BH 4, BH 5, BH 6, BH7, BH8, BH9, BH 10 and BH 11.
I want to show all the BH 3 entries together, all the BH 4 entreis together,
etc. If I sort accsending although it groups all the entries together I end
up with the column in the following order:
BH 10, BH 11, BH 3, BH 4, BH 5, BH 6, BH7, BH8 and BH9
How do I get it to show the BH 3 entries first and BH 10 and BH 11 last? I
need it in the correct order otherwise it affects the order when I create a
report graph i.e. the legend shows the series in the wrong order.

Thank you
Click to expand...
Click to expand...
 
Great it worked in terms of sorting the order of the data in athe query but I
thought it would apply when I created a chart from the correctly ordered
query - it doesn't and the legend / series are still in the wrong order.

Any idea about how to get round this?

The only thing I've managed it is to move the columns around in chart data
sheet - but again this is more fiddling.
 
You probably need to use the SortNo as the X-axis values rather than the
original Fields then use Automation to change the X-Axis Label and Legends
in the Chart.

However, this will involve a fair bit of coding ...

The easy alternative is to change the values to the same number of digits
for the nummeric part, e.g. BH 03, BH 04, ... , BH 10, BH 11. In this case,
you will be able to sort directly by the Field values and SortNo is not
needed. My guess is that the Chart will display the columns correctly also.
 
Back
Top