Conditional formatting update query - single digits to double digits

[roperj]

Here is my problem, I'm just unsure how to create the query to do it, but I have a good idea.


example of the data field - E-16-1-2 , E-2-2-2, W-1-5-16

In an access table I have a list of coded location names that are alpha numeric with dashes, a list of probably ~1500. They are the locations of plants in a field. Now, it would be really helpful if they were sorted numerically, in a descending order, however, the way access sorts them is based off of the first character in line, since it's not a true number. therefore

E-16-1-1 will always show up before E-2-2-2 because the 1 is a lower number. I figured the best way to solve this would be to convert all of the single digit numbers to double digits, so that all of the E-1-1-1 turn out as E-01-01-01.


Is this the right way to approach my problem? How would I specify to add a zero in front of a single digit in between dashes?

 

[RuralGuy]

Your field is a string (Text) field and that is simply an ASCII sort. My suggestion is either 1) create the numeric fields you need and populate then from the original field and then sort on the numeric field instead or 2) do the same thing in a query and sort on the created fields. Just my $0.02.

 

[roperj]

Your field is a string (Text) field and that is simply an ASCII sort. My suggestion is either 1) create the numeric fields you need and populate then from the original field and then sort on the numeric field instead or 2) do the same thing in a query and sort on the created fields. Just my $0.02


Well, the main reason is that fields get populated to a drop down list and external database program, not just for me to look at in the spreadsheet. I don't have as much control over the queries that access them programatically, so I feel like my easiest method would be to alter the entries themselves.

 

[RuralGuy]

What did you mean by "not just for me to look at in the spreadsheet"? Where did the spreadsheet come from?

 

[roperj]

The table is generated through an outside database/inventory program called SBI. On some of the product detail screens the places where information is entered and presented, it is difficult for the people doing data entry to do it quickly because of the method of sorting. My point was that when I view the data I'm not actually looking at it in Access in a spreadsheet, but in different drop down lists and forms, of which I'm not able to change the queries that produce the lists.


SO, basically my point was that I felt like this would be the only method I can see producing the results I need. The first response had some valid points, but seeing as how I can't use new queries to access the data, I need to manipulate the string field itself so the ASCII sort works the way I need.

 

[vbaInet]

You will need to split your string into 4 parts (i.e. four separate alias fields) and sort from the first (which would be "E") to the last. For the three number aliases, you must cast them (directly or indirectly) to type Number using CLng() or Val(). As RuralGuy mentioned, your numbers are being sorted using their ascii equivalents because they are seen as text.

 

[roperj]

What I think I want to try is basically an if then statement that looks through the string and if it finds any of the following "-1-", "-2-", "-3-", then it would change it to "-01-", "-02-, "-03-", and keep all of the related fields (it is a primary key field, but I could remove that) intact. My main problem right now is that I've totally forgotten the format and language to write this program. Even if someone posted a similar example that I could edit it would be enough.


I was going to do this as an update query, is this the best method? or do I need to use VBA scripting? SQL?

 

[vbaInet]

That won't make any difference. The result will still be string because of the hyphen and it will still perform an ascii sort. My last post contains information on how to go about getting it sorted in the right order.

Even if you decide to replace this "-" with this "" so that "E-16-1-2" becomes 1612 as a number (after casting of course), it will sort properly in number format however, if you had "W-1-6-12" and you perform that replace it will also become 1612 as a number. This is where your sort order fails. This was why I told you in my last post that you need to sort by the four split parts.

 

[roperj]

That won't make any difference. The result will still be string because of the hyphen and it will still perform an ascii sort. My last post contains information on how to go about getting it sorted in the right order.

Even if you decide to replace this "-" with this "" so that "E-16-1-2" becomes 1612 as a number (after casting of course), it will sort properly in number format however, if you had "W-1-6-12" and you perform that replace it will also become 1612 as a number. This is where your sort order fails. This was why I told you in my last post that you need to sort by the four split parts.

I don't think you understand what I'm trying to do and why yet. I cannot change the field from a string field, and need the locations to remain the way they are. HOWEVER, since they are being sorted with an ascii sort when they are sorted it comes out like this


E-1-1-1
E-11-1-1
E-16-1-1
E-3-1-1
E-9-1-1

however, if I did the formatting I'm suggesting it would change the values to sort like this

E-01-01-01
E-03-01-01
E-09-01-01
E-11-01-01


Do you understand now why I need to edit the values in this way? In the end, I need the values to match up with the field/row/location names and so they need to be in that format (a string) so I feel like I am stuck with an ascii sort. Also, I cannot change the query that actually accesses the data and populates the dropdown list that the user sees because we use an outside database interface program (access based). However, I can manipulate the data and tables however I need, so I think that this is the best way.


I am open to easier solutions, but I think this way is best, I just don't know how to phrase the statement (forgotten the language) and where to write it.

 

[vbaInet]

Well, if you think you have a solution that will work for you to get you the sort that is required then please do implement it. I've explained twice (already) how it will work and I understand exactly why the sort isn't working. If you feel what has been explained doesn't make sense or doesn't fit in to how you can achieve the sort, then there's nothing more I can say.

 

[roperj]

I actually have it mostly written but I'm stuck on using a wildcard character for the numeric digit between the two dashes. How would I pass "-*-" where * is the random digit in between to a variable? I guess I would probably need to remove the dashes as well.

 

[roperj]

Well, if you think you have a solution that will work for you to get you the sort that is required then please do implement it. I've explained twice (already) how it will work and I understand exactly why the sort isn't working. If you feel what has been explained doesn't make sense or doesn't fit in to how you can achieve the sort, then there's nothing more I can say.

I'm only doing that because I am unable to split up the field, I cannot change it from a string field, I can't split it into multiple fields, so I am stuck finding a way to make the ascii sort of a string field sort properly. If I could do it another way I would because it would be MUCH easier. maybe there is? I've read you ideas but I just don't see how they would work at all with what I've got, maybe I'm not understanding you.

 

[RuralGuy]

However, I can manipulate the data and tables however I need, so I think that this is the best way.

It might help us if you would explain this statement a little further.

 

[roperj]

It might help us if you would explain this statement a little further.

Well, basically I have full access to the database tables, and can change the data however I need, but I am unable to fundamentally alter the table structures themselves. I could split up the strWarehouseID field into multiple number fields, and sort them, but in the end I must recombine them back into a string field called strWarehouseID.



I can search the data myself just fine, the problem is on a user end, when they are accessing data through SBI, there are multiple drop down lists where they are trying to scroll through and find the data, and since it is a string field (though populated with numbers) there is an ASCII sort. All of these thousands of locations are already tied to products, so I can't completely change how the field map locations are set up, because the correspond with locations in the field.

 

[RuralGuy]

Well, basically I have full access to the database tables, and can change the data however I need, but I am unable to fundamentally alter the table structures themselves. I could split up the strWarehouseID field into multiple number fields, and sort them, but in the end I must recombine them back into a string field called strWarehouseID.

Does this mean you can *permanently* alter the strWarehouseID field so it would sort better?

 

[roperj]

Depends on what you mean by alter. For all intents and purposes I only have the ability to manipulate data, not table structure, but I do have full access to the database.

 

[RuralGuy]

Can you *permanently* change the field that contains "E-1-1-1" so it then contains "E-01-01-01" and that is all anyone will see from then on?

 

[roperj]

Yes, that is what I'm trying to do, I'm trying to create a function that goes through each row of strWarehouseID and does exactly that to each of the thousands of rows in that field. I have it mostly thought out and somewhat typed up



right nwo it searches through and tries to find the string "-#-" within the larger string of original_string which = strWarehouseID. However, I'm having trouble with it, because I need # to be a wildcard integer that finds a single digit in between two dashes, and passes the digit to a variable, adds a 0 to the front of it, and returns "-0#-" and loops through the whole record. I have most of it, but I don't know how to pass the wildcard digit into a variable because I can't (or can and don't know how) to specify that in a string.

 

[RuralGuy]

Let's make sure we have all of the parameters straight. We are just dealing with *one* field in *one* table and we can permanently alter that field to meet the specifications we have been discussing. We do not care how many records are involved as that just determines how long the process will take. Are *all* of the current fields in the A-n-n-n format? A = Alpha and n = numerical and "-" is a dash. Are there always 3 n values?

 

[RuralGuy]

Do we need to be concerned with something or someone changing the field back again?