CODEWITHSUNDEEP
excelms-accessbarcodescanning
FrosyFeet456
FrosyFeet456

Reputation: 349

Filter text out of barcode while scanning physical barcodes

I have a physical barcode on my packet. I need to filter out a text that says "CustID: " the whole barcode reads "CustID: 123456798".

My sheet contains "123456798" and I need the two to match. I have about 40000 barcodes so removing the "CustID: " while scanning each barcode simultaneously would work well and be faster for me.

I am using MS Access while scanning barcodes in the control source box I've tried typing =Replace("","CustID: ","") to remove the text but returns nothing when I try to scan the barcodes.

I would like the barcode to say "123456789" and not "CustID: 123456798" while filtering out each text before the barcode reads in my numbers.

My resolution was to put 

Dim lngCurBarCode1 As Long

If Me.txtRec.Text <> "" Then

    Me!txtRec.SetFocus
    If Me!txtRec.Text Like "RecID: *" Then
       Me!txtRec = Replace(Me!txtRec.Text, "RecID: ", "")
    End If
    lngCurBarCode1 = Me.txtRec.Text

End If

where textRec is the textbox

Upvotes: 0

Views: 252

Answers (2)

Tragamor
Tragamor

Reputation: 3634

With office 365, and as a much more generic process (i.e. multiple text portions or text which alters can all be caught); this can now easily be done with a formula.

=LET(arr, VSTACK(CHAR(SEQUENCE(26,,65)),{" ";":"}), REDUCE(A1, arr, LAMBDA(a,b,SUBSTITUTE(a,b,""))))

an array of A-Z is created and appended to an array of " " and ":"

The array values are then sequentially replaced by null text to leave the numeric components from the reference cell.

If you wanted to remove all the numbers you could do something similar:

=REDUCE(A1, SEQUENCE(10,,0), LAMBDA(a,b, SUBSTITUTE(a, b, "")))

Upvotes: 0

Lee Mac
Lee Mac

Reputation: 16015

I would suggest adding the following event procedure to either the After Update or On Change events of your MS Access textbox:

Private Sub YourTextbox_AfterUpdate()
    If YourTextbox Like "CustID: *" Then
        YourTextbox = Replace(YourTextbox, "CustID: ", "")
    End If
End Sub

Change YourTextbox to the name of your textbox.

Upvotes: 2

Related Questions