Non-Alphanumerics and sorting.

[EdFred]

I had been using previous versions of Access, and just recently "upgraded" to 2007. I used the search function, and didn't see exactly what I was looking for, so hopefully I don't get flamed too bad if I missed an obvious thread, but here's my situation:

In the previous versions of Access that I used I would get a sort order like this:


R-256
R-276
R-500
R2325
R2598

now I get the following

R2325
R-256
R2598
R-276
R-500

This is a primary key in my table, so stripping the field it apart, sorting, and reassembling is not really an option. I jumped from Office97 to 2007 with this particular database, so I am not sure when the non-alphanumerics were ignored when it came to sorting, but is there a way to get the hypens to be recognized when it comes to sorting?

Thank you.

 

[boblarson]

Now you are coming to understand, I hope, why it is not good to use that kind of thing as the primary key. A true number is best for that. However, all is not lost as you can set the query (use Queries, not tables) behind anything to sort by selecting only the digits after the letter.

 

[EdFred]

Now you are coming to understand, I hope, why it is not good to use that kind of thing as the primary key. A true number is best for that. However, all is not lost as you can set the query (use Queries, not tables) behind anything to sort by selecting only the digits after the letter.

Yeah, but it worked so well in 97 - and worked for 10 years that way. I haven't even had 2007 installed a week, and now it's little things here and there that grind me. So - dumb question - how do I get the hypens to be recognized in a query sort?

 

[boblarson]

I've said before to those who have asked how to do something, or I notice that they aren't doing something right, and they respond, "but it works" and I say, "yes it may, but at some time, somewhere, it will come back to bite you." Too bad you didn't have that knowledge back when it was created, but it happens. Now it's just a point of getting on with it and fixing the problems. The problem I see is that you can create a bandaid fix, which may not continue to work in the next versions, or you can take this as a cue to redesign the whole thing and bring it up to date and up to more normalized standards.

I've been there and had to do that, so I'm not just talking theoretically, but as someone who has gone through the pain, and I mean some serious pain, in redesigning things that I did earlier.

Good luck with it all. I mean that, as you do appear to have some challenging choices to make.

 

[EdFred]

I was afraid you were going to say that. Thing is, I'm in distribution, and I carry 20 lines of products, and have almost 25000 products that I import from vendors. Some vendors have hyphens, some don't. Some vendors have hypens in some of their products, while other products by the same vendor do not. The nice thing in 97 was that the R-xxx products are all similar so when I look through inventory they are all grouped together nicely. The Rxxx products are from different vendors and become interspersed in the R-xxx products. Actually the hyphens are used to break up the product ID so it visually looks ok. I also hyphenate the product id to make it so it's a bit easier to look at and see what it is. 6337-031-245-66-1 is a lot easier to look at and know exactly what it is vs 6337031245661. Granted, I have a description field, but that's not always looked, especially when I put in the porduct id on dropdown boxes on other forms.

Who's wonderful idea was it to ignore non a-n characters in sorting anyway? Even if I didn't use it as the primary key, it would still be nice to have the option to ignore, or not ignore non n-a characters when it comes to sorting.

Temporary solution:

I slapped a "band aid" on it and just did this:

Expr1: Asc(Left([ProductTable].[ProductID],1)) & Asc(Right(Left([ProductTable].[ProductID],2),1))

I then made this my first field, and sorted ascending.

 

[The_Doc_Man]

There is a default sorting order selection for your databases. Tools >> Options, I think. Look at an AC97 DB and one that gives you the "wrong" answer. It is possible that a new version has picked a different default sort order. (There is such a thing as a UNICODE sort, database sort, or simple ASCII collating sequence sort.) The behavior you report MIGHT (stress MIGHT) be due to the default sort order.