need to change the 14th digit in a 25 digit number series

  • Thread starter Thread starter jdt22
  • Start date Start date
J

jdt22

I have a field that is alphanumberic and need to change some digits in the
middle of the "numeric" portion of the field.

example: "000012000000110000000005Prep Exp-Insur. Umbrella Pol"

Counting from the left I need to increment the 14th digit by 1.

Is there any way to do this in access? I am using 2003 version.

Thank you for your help.
 
Try something like:
= left(myString,13) & cstr(cint(mid(myString,14,1))+1) & mid(myString,15)

What happens when that digit is 9 and gets incremented to 10?
You will have lengthened your code - is that alright?

Regards

Kevin
 
The concept would be to take the first 13 characters, append a new 14th
character based on the value of the existing 14th character, then append the
remainder of the text string. For example:

txtNewString = left(txtString,13) & val(mid(txtString,14,1))+1 &
right(txtString,len(txtString)-14)

You will need to think about what should happen if the 14th character is 9;
the code ahead will insert a '10', so the resulting string will be one
character longer than the original.

Your best bet would be to write a function to handle this, and then it would
be available everywhere. You could then also pass in a variable indicating
which character needs to be changed. You should test for a numeric digit,
and also decide what to do if there is a '9' in the position you want to
increase.

Good luck!
 
Maybe this is what is needed --
txtNewString = Val(left(txtString,14))+1 & right(txtString,len(txtString)-14)
 
Further to Daryl S's reply.

You could also use the mid$ function directly e.g.

Assuming strText is defined as a string and is populated with the string to
be changed;
strText = "000012000000110000000005Prep Exp-Insur. Umbrella Pol"

You could then use Mid$ as follows to change the 14th character;
Mid$(strText, 14, 1) = Trim(Str(Val(Mid$(strText, 14, 1)) + 1))

However as Daryl S also suggested you would need some code to cope with 9.

Hope this helps.
 
Back
Top