I am working on inventory management solution; to be effective, I need users to be able to create and populate records using a barcode scanner. A problem I've encountered is that one of my vendors uses different barcode schema for some of their own very-similar products. Initiating an inventory transaction with the Part# barcode is a piece of cake, but the Lot Number and Expiration date are combined in a second barcode that seemingly has variable numbers of check digits or checksum digits, and the date format varies from mm/dd/yy to yy/mm/dd depending on individual product lines and manufacturing locations. (They have an interest in keeping their scheme somewhat encrypted - they don't want layusers to be able to modify cartridge barcodes and extend expiration dates, since this would defeat a lock-out feature in their instrument software...)
It shouldn't be difficult to use Left, Right, or Middle to ferret out the character string comprising the expiration date, but how then to convert those strings into usable dates? It appears that all expiration dates fall on the last day of the given expiration month, so at worst a Case function with 12 statements should be able to substitute a standard format for any given non-standard format, but how then to account for the year? Any ideas?