Here is a mock up of the vba involved to do the transformation. Copy this and try it.
Code:
'---------------------------------------------------------------------------------------
' Procedure : mattbo
' Author : Jack
' Date : 25/01/2014
' Purpose : Routine to reassemble/reformat a code.
'
'https://www.accessforums.net/queries/query-trouble-left-join-null-condition-41008.html#post209252
'The only data I am manipulating as far as this database goes, is the serial number which manifests as;
'00GY20130001234' in Book1 and
'GY-13-00001234' in TblDirect Database.
'Book1 is created as an excel file. I import it into the database by changing it to a .csv file,
'with the serial number in the first column. I import it as fixed length which allows
'the serial number to be dissected into
' Field 1 (00)
', Field 2 (GY)
', Field 3 (20)
', Field 4(13)
', Field 5 (00001234), and the remaining fields which are largely ignored.
'---------------------------------------------------------------------------------------
'
Sub mattbro()
Dim Bk1_Serial As String
10 Bk1_Serial = "00GY20130001234"
Dim sTarget As String
20 On Error GoTo mattbo_Error
' Parse the book1 serial
' Positions 3 and 4, "-", positions 7 and 8, "-" and 9 thru 15 with extra 0 in left
30 sTarget = Mid(Bk1_Serial, 3, 2) _
& "-" _
& Mid(Bk1_Serial, 7, 2) _
& "-" _
& Format(Mid(Bk1_Serial, 9, 7), "00000000")
40 Debug.Print sTarget
50 MsgBox Bk1_Serial & " becomes " & vbCrLf & sTarget
60 On Error GoTo 0
70 Exit Sub
mattbo_Error:
80 MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure mattbo of Module AWF_Related"
End Sub
I do have some questions for you.
You say you import, but do you clear out the data before you import the next time?
Is the Table Direct data cleared out before you do the next updates/appends?
Have you considered just linking to the Excel file and not importing it into Access?
Your data may be confidential to you, but it is quite unintelligible to most of us. If you could supply us some csv data, we can work on your JOIN issue.
Good luck.