Solved DLookup Returning Null (1 Viewer)

[LiziM]

Hi all I have worked extensively with Excel VBA, but am having some trouble with VBA in Access, I hope you can help.

A user inputs a product's barcode on a form (frm_scan_IN) then I am using a DLookup to return the category for the product ("ProductCategory"), based on the first few characters from its barcode ("ProductBarcodeIdentifier"), both fields are in the table "tblProducts".

The DLookup returns a Null value, despite there being a record with the specified ProductBarcodeIdentifier. I think it may be an issue relating to data types, however, I have "Dim"ed the variable varCategory and varBarcodeIdentifier as strings, since the fields for Category and BarcodeIdentifier in tbl_Products are of type "short text". I have included my code and a screenshot of the tbl_Products design view so you can see the data types. Let me know if you need more info!

varBarcodeIdentifier = Left([Forms]![frm_scan_IN]![tb_ProductSerialNumber], Len([Forms]![frm_scan_IN]![tb_ProductSerialNumber]) - 8)
varCategory = DLookup("[ProductCategory]", "tbl_Products", "[ProductBarcodeIdentifier]= 'varBarcodeIdentifier'") 'varCategory=Null, which it shouldn't

 

[CJ_London]

varCategory = DLookup("[ProductCategory]", "tbl_Products", "[ProductBarcodeIdentifier]= 'varBarcodeIdentifier'")

that is looking for a record which contains 'varBarcodeIdentifier', not it's value

you need to reference the value

varCategory = DLookup("[ProductCategory]", "tbl_Products", "[ProductBarcodeIdentifier]= '" & varBarcodeIdentifier & "'")

 

[LiziM]

that is looking for a record which contains 'varBarcodeIdentifier', not it's value

you need to reference the value

varCategory = DLookup("[ProductCategory]", "tbl_Products", "[ProductBarcodeIdentifier]= '" & varBarcodeIdentifier & "'")

Awesome, working like a dream. Thanks CJ_London, I understand now!