Dissecting and sorting
Yesterday I helped someone check our inventory. We have plantwide physical inventory coming up at the end of the month and we are trying to clean out all the really obvious garbage from our system. By “garbage” I mean things like Part A in location 8 with a quantity on hand of 0, and in location 2 with a quantity on hand of 4, and location 5 with a quantity on hand of 326.
This fellow B. E. who works in the stockroom asked for my help getting a list of what the system thought was in a certain area so he could consolidate things and elimate null locations and so forth. Retrieving the locations, part numbers, and (nominal) quantities on hand was straightforward, but then he wanted the results sorted in a specific way.
The location designations are in the following format: O4GH9. For our area of interest, the first letter (O) never varies and the second letter (G) never varies. Both of the numbers could be two digit numbers (4 could be 14, 9 could be 11). B.E. wanted the locations sorted first by first number, and then by the last letter and number.
For MS Access, a number is a different “thing” than a letter, a different type of data. But numerals can be used in text (as letters, so to speak). A string of characters that contains letters and numbers will be considered letters, and the numbers will not be recognized as numbers per se. What this means is that if you have three strings, 1A, 2A, and 11A, Access will consider each ‘1’ to come before any letter and before the number two, resulting in this sort: 1A, 11A, 2A. You may have seen this kind of behavior when you were working in Excel or some other program, too.
So I had to add some extra columns in my query that broke out bits of these locations and used them to sort. And I did it, pretty quickly too. Here’s what I did:
I actually constructed the second sort first because that’s the first sort he told me about, and I use a bit of the second one in the first one. First, I found the second letter that never changes by using the InStr() function, which returns the number of characters into the string that the searched character is found: InStr([location], “G”).
To get my sort key I took a piece of the string starting with that letter using the Mid() function, which begins at the specified number of characters into the string and continues for the specified number of characters (or until the end if you do not specify. I did not specify an end location and I am repeating the function above (which gives me my starting location) inside this formula:
Mid([location], InStr([location], “G”)). On our example location, this would result in “GH9.”
The second sort I built (which ultimately was the first-priority sort) was more complicated. I started with the Mid function to get a piece of the string, skipping the first letter which was always the same (so starting on the second character): Mid([location], 2, ….
Then I needed to know how many more characters to go forward. To know that I needed to know how far away the next letter than never changed was. Finding that next letter means I need to use InStr: Mid([location], 2, InStr(…
But I don’t want to use InStr to search my whole entire location string. I only want to search the part ahead of that first letter. So that would be the entire string starting from the second charcter, using Mid: Mid([location], 2, Instr(Mid([location],2)…
And what I am looking for is that second letter that never changes: Mid([location], 2, Instr(Mid([location],2), “G”) …
Close off our original Mid function: Mid([location], 2, Instr(Mid([location],2), “G”))
Running this gave me a result the included the second letter that never changed: from our example, 4G. This is because I was starting my search after the first letter that never changed, on the first character of the number. So 4 = 1st character, G = 2nd character, and that tells the mid function to proceed from the starting point 2 characters. So I told it to back up one: Mid([location], 2, Instr(Mid([location],2), “G”)-1)
Now I had my number, but because the numeral came out of text-numeral string, Access still thought of it as a letter. So I converted it to an integer using CInt(): CInt(Mid([location], 2, Instr(Mid([location],2), “G”)-1))
And that was easy. Today’s job was hard. So I won’t explain it.