Hi xmattxman, My first thought on this is to use string handling as the solution. However, I found the suggestions on using Patterns and Like interesting, and have to admit not having using them. Clearly I have not read the Language Reference as well as I thought!
Presumably you'll be taking the long code from Table so all you need to do is to pass that variable to the Function GetShortCode() at any point in your code. (Presuming of course that you place the Function in your own library for the application in Modules.)
You could also update a table using an Update Query by calling the function in the UpdateTo section in the Query using the LongCode in the table as the parameter.
The following Function will return the part of the code required. Clearly from the other posts there is never one way in Access, or indeed any language to solve a problem. It is always the method that you prefer to use,
Function GetShortCode(LongerCode As String) As String
'
Dim mLongCode, mCodeCut As String
Dim mLenSC, mPos09, mLenCut As Integer
'
mLongCode = LongerCode
mLongCode = Trim(mLongCode)
mLenSC = Len(mLongCode)
mPos09 = InStr(mLongCode, "09")
mLenCut = mLenSC - mPos09 + 1
mCodeCut = Right(mLongCode, mLenCut)
'
For X = 1 To mLenCut
If Mid(mCodeCut, X, 1) = Space(1) Or Mid(mCodeCut, X, 1) = ")" Then
GetShortCode = Left(mCodeCut, (X - 1))
X = mLenCut + 1
End If
Next X
'Returns short code
'!NOTE! No error checking or data validation exists here, needs to be added
'
End Function ' GetShortCode()