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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

SteveL13Author 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.
You can add function like:
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

Open in new window

to global module and call it in any update query
UPDATE Table1 SET Table1.Field1 = remove_part([Field1]);

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
SteveL13Author 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"

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

SteveL13Author Commented:
A couple examples that didn't covert:

"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
SteveL13Author Commented:
Yes,  You are correct.  I have to figure something else out.  But your answer is good to hang onto for future.
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.