Conditional formatting update query - single digits to double digits

[roperj]

There are several records that do not fit the format. They are just basic descriptions (warehouse, cooler, seed lab, etc) which is why I was going for something that finds single digits in between dashes. The format is basically what anyones enters, but if this was fixed people would only be entering it in the new way. Also, I could run the function again to clear it up.

 

[RuralGuy]

So can we say that if the field does not have any dashes in it then return it as is, unmodified? Are you in control of the entry application enough to catch this during entry and fix it?

 

[RuralGuy]

I lean toward using a neat VBA Function called Split() with the "-" as the separator.

 

[vbaInet]

I lean toward using a neat VBA Function called Split() with the "-" as the separator.

Which was what I was explaining in post #6. The four fields will be aliases. It will not affect the table data.

 

[RuralGuy]

But it sounds like the RecordSource (query) can not be modified but the table can so I was suggesting using the Split() function in a UDF that is used in a separate Update Query. It would make it easy to return the string with each one of the numerical values padded out to 2 places.

 

[vbaInet]

But it sounds like the RecordSource (query) can not be modified but the table can so I was suggesting using the Split() function in a UDF that is used in a separate Update Query. It would make it easy to return the string with each one of the numerical values padded out to 2 places.

I was reiterating to the OP not to you Allan

Yes you're right, if the table can be updated then an update (and probably an insert) query would do the job after being split. Then the OP can concatenate the fields to the original format in a query.

 

[RuralGuy]

I'm sure it can all be done with one "[TheField] = UDF([TheField])" Update Query and your done.

 

[RuralGuy]

Here's the UDF I had in mind:

Public Function ConvertIt(InValue As String) As String
'-- Return "E-1-1-1" as "E-01-01-01"

   Dim InString() As String

   If InStr(InValue, "-") = 0 Then
      '-- No "-" in this string, return the original string.
      ConvertIt = InValue
   Else
      InString = Split(InValue, "-", -1)
      ConvertIt = InString(0)           '-- Start with the alpha value
      ConvertIt = ConvertIt & "-" & Format(InString(1), "00")
      ConvertIt = ConvertIt & "-" & Format(InString(2), "00")
      ConvertIt = ConvertIt & "-" & Format(InString(3), "00")
   End If

End Function

 

[vbaInet]

I'm sure it can all be done with one "[TheField] = UDF([TheField])" Update Query and your done.

Oh, yes! With the "0" it should sort appropriately.

 

[roperj]

I'm having some trouble implementing the UDF you posted. I placed it in a module in a test db I made with the table, and then made an update query that says

UPDATE [copy of tblICInventory]

SET [strWarehouseID] = ConvertIt([strWarehouseID]);


It ran the first time but threw an error, I think because of some fields which had strangely formatted data. However, now when I try to run the query, it tells me I have an undefined expression in the query. I have the ConvertIt function in a module in the db (the only one), why would it throw this error?

 

[boblarson]

Did you perhaps accidentally name the module ConvertIt as well? The module name should be different from the procedure.

 

[roperj]

No, the module is simply called Module1, its in a seperate test db that contains only this module, and the only thing in it is this:



"

Option Compare Database


Public Function ConvertIt(InValue As String) As String
'-- Return "E-1-1-1" as "E-01-01-01"

Dim InString() As String

If InStr(InValue, "-") = 0 Then
'-- No "-" in this string, return the original string.
ConvertIt = InValue
Else
InString = Split(InValue, "-", -1)
ConvertIt = InString(0) '-- Start with the alpha value
ConvertIt = ConvertIt & "-" & Format(InString(1), "00")
ConvertIt = ConvertIt & "-" & Format(InString(2), "00")
ConvertIt = ConvertIt & "-" & Format(InString(3), "00")
End If

End Function
"

 

[boblarson]

Could there be any null values for the field there?

 

[RuralGuy]

What did you name the module? It should be something like basFunctions; anything but ConvertIt will work.

 

[roperj]

I got it working, I think something just went wrong when it tried to debug too many errors that I couldn't cancel out of. I've been revising that statement, the first one ended up cutting off the last digit of a lot of the data entries.


Another problem is that this data isn't normalized at all, its something I'm attempting to do with this script. TThere are entries that are like W10-10-10 or W-10/6/7 or W-4-5-15-16, and so I've been trying to extend the script to accomodate these different types. The first run through cut down everything to something like W-10-10 unfortunately.


even if I get it working im terrified to run it on the actual database, though I would definitely back it up first.

 

[RuralGuy]

Backing up is *always* a good plan. Good luck.