Sort order -- Alphanumeric beyond 1st digit

[lbcgav]

A relative newbie to Access here. I'm tring to sort according to product ID where the product ID is text and often alphanumeric. For example EH1200, EH250, EH2600. Right now, the sort order is EH1200, EH250, EH2600. How can I get it to sort it in the following order: EH250, EH1200, EH2600? In other words, I want it to sort according to the ENTIRE number value, not just the first digit or two. Your help would be greatly appreciated.

 

[Brianwarnock]

product ID is text and often alphanumeric

That's a bit worrying it suggests that your examples are not giving the whole story, if they were then you could extract the number with
Val(Mid([productid],3,Len([productid])-2))
and sort on it.

Brian

 

[ajetrumpet]

A relative newbie to Access here. I'm tring to sort according to product ID where the product ID is text and often alphanumeric. For example EH1200, EH250, EH2600. Right now, the sort order is EH1200, EH250, EH2600. How can I get it to sort it in the following order: EH250, EH1200, EH2600? In other words, I want it to sort according to the ENTIRE number value, not just the first digit or two. Your help would be greatly appreciated.

Are the first two characters of these values always alpha characters? If they are, you can use the advance filter option to do this. Type this in the field (just like a query):

Right([your field here], len([your field here]) - 2)

and toggle the filter option.

 

[Brianwarnock]

Not quite Adam

Right([your field here], len([your field here]) - 2)

and it would still need Val to convert to numeric to sort correctly.

Brian

 

[ajetrumpet]

whatever Brian.

You jumped me anyway.

I sure hope he looks at your instead of mine.

 

[ajetrumpet]

Brian,

did you post that before I made corrections? You posted the exact same syntax as what I have. Hmmm...

 

[lbcgav]

Thank you for the quick replies.

Unfortunately, not all the product ids start with 2 letters. Some are purely numerical, e.g., 2021. Some end with letters. I noticed that I get the same problem in Excel. If I sort the following: 1500, 1200, 500, 200, Excel sorts it as 200, 500, 1200, 1500, which is the way I want it sorted. If, however, I add "EH" in front of the numbers, it then sorts it as EH1200, EH1500, EH200, EH500. I feel I'm missing something here. Again, your suggestions would be greatly appreciated.

 

[ajetrumpet]

Gav,

I just had a crazy idea. I think it might work. Try using a query to sort this stuff. Include in it, one column that extracts the numeric portion of the value. Sort the query by this column, in Ascending order, and make sure to hide it (uncheck the "show" box in the design view part of the query). The expression for the column in the SELECT portion of the SQL statement would be this:

Extraction([ProductID]) As [Sort Field]

The function "Extraction" would then be written like this:

Function extraction(MYproductID As String) As Integer

Dim i As Integer

  for i = 1 To Len(MYproductID)
    If IsNumeric(Mid(MYproductID, i, 1)) then
      extraction = extraction & Mid(MYproductID, i, 1)
    End If
  Next i

End Function

I'm thinking an entire query statement would look like:

SELECT ProductID
    FROM YourTable
        ORDER BY extraction([ProductID]);

See what you think of that. Might just be worth a try...

 

[gemma-the-husky]

once you put letters in a number you no longer have a number - you now have a string, which access tries to sort in an ALPHABETIC way, rather than a NUMERIC way.

hence
EH1
EH10
EH2

is a "dictionary order", but this is not what you want


to sort in number order you either need

a) to ensure all strings are the same length, then you get

EH01
EH02
EH10

and your sort works ok OR


b) find a way of extracting the number as a temporary field, and sort on that

so you get
1 (EH1)
2 (EH2)
10 (EH10)

which also works ok

unfortunately this last bit is slightly harder to do. Access has a "val" function to change a string into a number, but unfortunately it doesnt extract the numbers in quite the way you need (look at help to see what it does). Hence all the advice here is based on ways of manipulating your string to try and extract the number bit.

I hope this helps you understand exactly what is going on

-------
its another reason why data analysis and normalisation is so important (this isnt really a normalisation issue, but its similar in concept). In your case its probably just a discussion point, as its probably too late to change now, but it does mean that even a product code structure should be carefully considered before implementation.

 

[lbcgav]

once you put letters in a number you no longer have a number - you now have a string, which access tries to sort in an ALPHABETIC way, rather than a NUMERIC way.

hence
EH1
EH10
EH2

is a "dictionary order", but this is not what you want


to sort in number order you either need

a) to ensure all strings are the same length, then you get

EH01
EH02
EH10

and your sort works ok OR


b) find a way of extracting the number as a temporary field, and sort on that

so you get
1 (EH1)
2 (EH2)
10 (EH10)

which also works ok

unfortunately this last bit is slightly harder to do. Access has a "val" function to change a string into a number, but unfortunately it doesnt extract the numbers in quite the way you need (look at help to see what it does). Hence all the advice here is based on ways of manipulating your string to try and extract the number bit.

I hope this helps you understand exactly what is going on

-------
its another reason why data analysis and normalisation is so important (this isnt really a normalisation issue, but its similar in concept). In your case its probably just a discussion point, as its probably too late to change now, but it does mean that even a product code structure should be carefully considered before implementation.

Thank you for this explanation. This is what I thought was going on and I did try the Val function but it would not give me the values I needed for the product ids starting with letters.

I know this sounds quite elementary, but could someone tell me how to create a function. I found out how to create the query in SQL format, but I don't know how to create the function in the example given above. I tried to create a VB script in Modules, but it gave me an error when I tried to use the code in the example above. I do appreciate the help I've received so far and the quick responses.

 

[ajetrumpet]

but could someone tell me how to create a function.

These keystrokes should get the job done quite Well:

Alt + F11 > Insert Menu > Module > Copy / Paste Function code from this post to your screen > Run Query

 

[lbcgav]

These keystrokes should get the job done quite Well:

Alt + F11 > Insert Menu > Module > Copy / Paste Function code from this post to your screen > Run Query

Thank you. I got the query to work according to your instructions. Thank you very much.