# How rename part number in two tables

I have a table that contains a field for part numbers.  It is a text field.  But I need to somehow, I suppose via an update query, rename the part numbers.

Here are a few example of the current part number and the expected renamed part number:

Current                              Expected
N1100-12R05-F                 N1100-12-F     (Note that the R05 has been removed)
N1375-12X75-F                 N1375-12-F     (Note that the X75 has been removed)
N1202-12PTR4-F               N1202-12-F     (Note that the PTR4 has been removed)

So in essence, the characters following the 2nd digit after the 1st hyphen through the character prior to the 2nd hyphen have to be removed.  In some cases this would be 3 characters and in other cases it would be 4 characters.

How can I do this?
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Author Commented:
I forgot to tell you the rest of the challenge.  There is a 2nd table that has the same part "numbers".  But in that table the part number may be repeated in several records.  The same new name for the part number in the 1st table has to be applied to the 2nd table part numbers.
0
Commented:
``````Function remove_part(ByVal Str As Variant) As Variant
Dim Arr() As String
remove_part = Str
If IsNull(Str) Or Len(Str) < 3 Then Exit Function 'String is too short
Arr = Split(Str, "-")
remove_part = Arr(0) & "-" & Left(Arr(1), 2) & "-" & Arr(2)
End Function
``````
to global module and call it in any update query
UPDATE Table1 SET Table1.Field1 = remove_part([Field1]);
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Almost.  But I got an error...

"Microsoft Access can't update all the records in the update query....   440 record(s) due to key violations"

???
0
Author Commented:
A couple examples that didn't covert:

C1875-12R06-F
C1960-12R06-F
C2040-12R06-F
0
Commented:
"Microsoft Access can't update all the records in the update query....   440 record(s) due to key violations"  -
this error is a result of your indexes. May be after conversion you get duplicate values, which are not allowed
0
Author Commented:
Yes,  You are correct.  I have to figure something else out.  But your answer is good to hang onto for future.
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.