Trimming a data entry into a text box in Access au

Question

I have a simple Access database that has image pathways identified by part numbers. Right now people can enter the part number manually but I want them to be able to scan a barcode that enters the part number. Unfortunately, the barcode contains additional content other than the part number.

For example: 79|99999-ID|Lot:9999|Exp:31-June-1999

Should be trimmed down to "99999-ID".

Option Explicit  

Private Sub BTN_Search_Click()  
Dim SQL As String  

SQL = "SELECT Query65.ITEM_NUMBER, Query65.PLANNER_DESCRIPTION " _  
    & "From Query65 " _  
    & "WHERE [ITEM_NUMBER] = '" & Me.txtPartNumber & "' " _  
    & "ORDER BY Query65.ITEM_NUMBER "  

Me.SubPlannerSubForm.Form.RecordSource = SQL  
Me.SubPlannerSubForm.Form.Requery  

End Sub```

Answer 1

Public Function SplitBarcodeToPartNo(ByVal Barcode As String) As String
    SplitBarcodeToPartNo = Split(Barcode, "|")(1)
End Function
Private Sub TestSplitBarcodeToPartNo()
    Debug.Print SplitBarcodeToPartNo("79|99999-ID|Lot:9999|Exp:31-June-1999")
End Sub

Split()splits the string to an Array at the | and as the PartNo is the second item just fetch it by Array-Index 1 (As the first isSplit(Barcode, "|")(0), Last (Date) is Index 3 (fourth item) )

Any reason not to store the other infos in the DB as they are scanned allready?