Character Replacement by Position in String

[J Bhujanga]

I have a data field that is 20-character string called [Veh_Impact]. Each character in the string starts off by default as a zero. "00000000000000000000"
Each of these characters happens to stand for a particular location on a car body. A zero in a given position means that part of the car is not damaged, a 1 means light damage, a 2 means moderate and a 3 means severe damage.
So a string for a car that has been in an accident might look like this: "00000123300000000222"
We get data from the state of Louisiana that has a whole different coding system. A "G" for example corresponds to damage at positions 5 and 6. And another field will indicate the extent of that damage. They can have several such sets for a given vehicle.
So I run this through a converter and end up with three pieces of information for each set of damage codes which has a location, which in the example would be 5, a length which would be 2 (i.e. 5 and 6) and the damage level, which we'll say was something equivalent to our damage level 3.
So, with this bit of data I want to change the default string to "00003300000000000000". Then with any additional set of codes they provided I'd continue to update the string.
The problem is I can't figure out a function that does this. Essentially I want to say:
With the string [Veh_Impact] ("00000000000000000000"), begin at position 5 and replace the next 2 characters with 3's.

I envision something like the MID function, but one that takes an action rather than just querying, like if you could say MID([Veh_Impact],5,2)="33"
How would one do something like this?

[June7]

Break the string at the desired positions then concatenate the parts back together. Example:

x="00000000000000000000"
x = Left(x,4) & "33" & Mid(x,7)

[CJ_London]

or you can convert the string to an array and work on that

Function insertChars(str As String, chars As String, pos As Byte) As String
Dim arry() As String
Dim i As Integer

    str = StrConv(str, vbUnicode)
    arry = Split(left(str, Len(str) - 1), vbNullChar)
    For i = 1 To Len(chars)
        arry(pos + i - 2) = Mid(chars, i, 1)
    Next i
    insertChars = Replace(Join(arry), " ", "")
    
End Function


Sub tst()
Dim s As String

   s = "00000000000000000000"
   s = insertchars(s, "33", 5)
   Debug.Print s '00003300000000000000

   s = insertchars(s, "21", 10)
   Debug.Print s '00003300021000000000

End Sub

[orange]

I agree with the options provided by June and Ajax, but I wonder if this is just a temporary fix. It seems to me that you and those you interact with have different codification mechanisms and you are attempting to solve it "one identified position and one partner at a time".

My best guess, since I don't know your business, is you need a general solution that involves:
-identifying all your customers/partners (those with whom you interact)
-identifying their codification and definition ( what the code means)
-devise a converter function (algorithm) that takes incoming codes to a standard unified code.

This will allow you and others to communicate using some standard terminology.

I found this link/info via Google, and this is the kind of concept I think you and the others you deal with have to address. If this current requirement ( code translation) will be/is an ongoing situation, the sooner you resolve it "generally" to include current and future partners, the less effort and confusion involved.

This link regarding accident collision damage codification.

[J Bhujanga]

Thanks for your replies. I think for my purposes June's idea will work the best as I want to build it into an update query.
Unfortunately our data is provided in the form of various city and state accident report databases, so we are locked into accepting the data in their particular formats.
Thanks.

[CJ_London]

not a problem, its your project, you need to decide what is best for you.

good luck