Barcode - Regex - DLookup? (1 Viewer)

1kelly2dc

New member
Local time
Yesterday, 17:20
Joined
Jun 29, 2016
Messages
3
I would like to user Regex to to parse out additional digits in a scanned barcode and compare the result to the data in a column of a separate table.

Example
Scanned barcode: 030076126007348
Table contains: 761260073
I was able to find how to for the regex evaluation but not how to take the result and use it in a dlookup.

Thanks to anyone who can help,
_D
 

Cronk

Registered User.
Local time
Today, 11:20
Joined
Jul 4, 2013
Messages
2,770
On the basis that you want to strip off the first 4 characters and take the next 9 characters, use
mid(strBarCode,5,9)
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 11:20
Joined
Jan 20, 2009
Messages
12,849
I would like to user Regex to to parse out additional digits in a scanned barcode and compare the result to the data in a column of a separate table.

Example
Scanned barcode: 030076126007348
Table contains: 761260073
I was able to find how to for the regex evaluation but not how to take the result and use it in a dlookup.

Forget the Dlookup and join the tables. A function can be used in the join in the SQL View. It isn't supported in Design View.
 

1kelly2dc

New member
Local time
Yesterday, 17:20
Joined
Jun 29, 2016
Messages
3
Forget the Dlookup and join the tables. A function can be used in the join in the SQL View. It isn't supported in Design View.

Ok, so I would need to give the field in my form a control that is a query.The query should include a inner join between the the table and colMatches from the following vba:

Set colMatches = objRegExp.Execute(myString) ' Execute search.
For Each objMatch In colMatches ' Iterate Matches collection.

Is that correct?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:20
Joined
May 7, 2009
Messages
19,169
Dlookup("FieldToReturn", "Table", "Instr('" & scannedbarcode & "', [barcodeField])<>0)")
 

1kelly2dc

New member
Local time
Yesterday, 17:20
Joined
Jun 29, 2016
Messages
3
Dlookup("FieldToReturn", "Table", "Instr('" & scannedbarcode & "', [barcodeField])<>0)")

How do I use the result of the regex comparison in the dlookup? Is objMatch the "scannedbarcode"?
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 11:20
Joined
Jan 20, 2009
Messages
12,849
I think you need to better explain the context of what you are actually trying to achieve rather than asking for advice on how you apparently presume it should be done with Collections and DLookups.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:20
Joined
May 7, 2009
Messages
19,169
ok, is your goal find a match on a field in a table for your scanned barcode?
then only simple DLookup will do (as my example post)

if you want to return the corresponding barcode:

Dlookup("[barcodeField]", "Table", "Instr('" & scannedbarcode & "', [barcodeField])<>0)")

say you have a form for your barcoding, and textbox (txtScannedBarCode) is where the scanned barcode is placed.
on another textbox you can set the recordsource to:

Dlookup("[barcodeField]", "Table", "Instr('" & [txtScannedBarCode] & "', [barcodeField])<>0)")
 

Users who are viewing this thread

Top Bottom