Sorting Problem

[alecgreen]

Hi,

I have a table with various stock locations in (A1 to A200), but when
I sort the data in a query it sorts in the order A1, A10, A100, A2
etc. How can I sort it in to a logical order please (A1,A2 etc).

Many Thanks

Alec

 

[John W. Vinson]

Hi,

I have a table with various stock locations in (A1 to A200), but when
I sort the data in a query it sorts in the order A1, A10, A100, A2
etc. How can I sort it in to a logical order please (A1,A2 etc).

Many Thanks

Alec

That sort order IS CORRECT.

Since the field is a Text field, it's sorting in Text order; the order A1,
A10, A100, A2 is being treated exactly the same as the order Ab, Aba, Abaa,
Ac. You wouldn't want to sort Ab, Ac, Ax, Aba, Abb would you?

If you want to sort by numerical order instead, and the field always has a
single text charactar followed by number, use the Val() function to create a
number:

ORDER BY Left([StockLocation], 1), Val(Mid([StockLocation], 2)

If the number might be embedded elsewhere in the location - e.g. if you have
values like A100, BCL3, BCL255, BA35 - you'll need to define your desired
sorting rule more clearly. You may want to store the text prefix in one field
and the numeric suffix in another.

On the other hand, if all locations have an A as the first character, consider
not storing the letter A at all - just use a Number type field and display it
with a Format such as

"\A#"

to display the letter A followed by the numeric value.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[alecgreen]

Hi,

I have a table with various stock locations in (A1 to A200), but when
I sort the data in a query it sorts in the order A1, A10, A100, A2
etc. How can I sort it in to a logical order please (A1,A2 etc).

Many Thanks

Alec

That sort order IS CORRECT.

Since the field is a Text field, it's sorting in Text order; the order A1,
A10, A100, A2 is being treated exactly the same as the order Ab, Aba, Abaa,
Ac. You wouldn't want to sort Ab, Ac, Ax, Aba, Abb would you?

If you want to sort by numerical order instead, and the field always has a
single text charactar followed by number, use the Val() function to create a
number:

ORDER BY Left([StockLocation], 1), Val(Mid([StockLocation], 2)

If the number might be embedded elsewhere in the location - e.g. if you have
values like A100, BCL3, BCL255, BA35 - you'll need to define your desired
sorting rule more clearly. You may want to store the text prefix in one field
and the numeric suffix in another.

On the other hand, if all locations have an A as the first character, consider
not storing the letter A at all - just use a Number type field and display it
with a Format such as

"\A#"

to display the letter A followed by the numeric value.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Hi

Thanks for your help, but I can not get it to work!, please can you
give me an example on how to use please.

Thank You

Alec

 

[John W. Vinson]

Hi,

I have a table with various stock locations in (A1 to A200), but when
I sort the data in a query it sorts in the order A1, A10, A100, A2
etc. How can I sort it in to a logical order please (A1,A2 etc).

Many Thanks

Alec

That sort order IS CORRECT.

Since the field is a Text field, it's sorting in Text order; the order A1,
A10, A100, A2 is being treated exactly the same as the order Ab, Aba, Abaa,
Ac. You wouldn't want to sort Ab, Ac, Ax, Aba, Abb would you?

If you want to sort by numerical order instead, and the field always has a
single text charactar followed by number, use the Val() function to create a
number:

ORDER BY Left([StockLocation], 1), Val(Mid([StockLocation], 2)

If the number might be embedded elsewhere in the location - e.g. if you have
values like A100, BCL3, BCL255, BA35 - you'll need to define your desired
sorting rule more clearly. You may want to store the text prefix in one field
and the numeric suffix in another.

On the other hand, if all locations have an A as the first character, consider
not storing the letter A at all - just use a Number type field and display it
with a Format such as

"\A#"

to display the letter A followed by the numeric value.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Hi

Thanks for your help, but I can not get it to work!, please can you
give me an example on how to use please.

Thank You

Alec

Ummm... the OrderBy clause I suggested above should work: what do you get if
you edit the SQL of your query to

SELECT <whatever> FROM <wherever> WHERE <criteria> ORDER BY
Left([StockLocation], 1), Val(Mid([StockLocation], 2)

Perhaps you could post the entire SQL of the query you're running.

My second suggestion would require restructuring your table - more work but
perhaps worthwhile; I don't know your data or your constraints so I don't know
whether it would be or not.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[alecgreen]

Hi,
I have a table with various stock locations in (A1 to A200), but when
I sort the data in a query it sorts in the order A1, A10, A100, A2
etc. How can I sort it in to a logical order please (A1,A2 etc).
Many Thanks
Alec
That sort order IS CORRECT.
Since the field is a Text field, it's sorting in Text order; the orderA1,
A10, A100, A2 is being treated exactly the same as the order Ab, Aba, Abaa,
Ac. You wouldn't want to sort Ab, Ac, Ax, Aba, Abb would you?
If you want to sort by numerical order instead, and the field always has a
single text charactar followed by number, use the Val() function to create a
number:
ORDER BY Left([StockLocation], 1), Val(Mid([StockLocation], 2)
If the number might be embedded elsewhere in the location - e.g. if you have
values like A100, BCL3, BCL255, BA35 - you'll need to define your desired
sorting rule more clearly. You may want to store the text prefix in one field
and the numeric suffix in another.
On the other hand, if all locations have an A as the first character, consider
not storing the letter A at all - just use a Number type field and display it
with a Format such as
"\A#"
to display the letter A followed by the numeric value.
--
             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Hi

Thanks for your help, but I can not get it to work!, please can you
give me an example on how to use please.

Thank You

Alec

Ummm... the OrderBy clause I suggested above should work: what do you getif
you edit the SQL of your query to

SELECT <whatever> FROM <wherever> WHERE <criteria> ORDER BY
Left([StockLocation], 1), Val(Mid([StockLocation], 2)

Perhaps you could post the entire SQL of the query you're running.

My second suggestion would require restructuring your table - more work but
perhaps worthwhile; I don't know your data or your constraints so I don'tknow
whether it would be or not.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -

Thank you!, works a treat now - sorry my mistake first time.