Filter Query by Character Format?

[BillieBurgess]

Hello,
I have a table of Scanned Serial Numbers. Our Serial Numbers are 12 characters and are alphanumeric. Sometimes the scanner will create a bad scan, it misses or adds characters, or adds a $ sign or some other symbol. I need to filter out the bad scans, and update them as such. I cannot create a input mask to automatically throw them out because, we need to identify the bad scans and then go look for the merchandise and re-scan them. I use the LEN expression to filter for all SN that were scanned in that have more or less then 12 characters. I cannot figure out how to filter for the symbols

Your help is appreciated.

My current query is below it is not working:

UPDATE PurchaseOrderScan SET PurchaseOrderScan.BadScan = Yes
WHERE (((PurchaseOrderScan.BadScan)=No) AND ((PurchaseOrderScan.SN) Not Like "*[A-Z]")) OR (((PurchaseOrderScan.BadScan)=No) AND ((PurchaseOrderScan.SN) Not Like "*[A-Z]" And (PurchaseOrderScan.SN) Not Like "*[1-10]*")) OR (((PurchaseOrderScan.BadScan)=No) AND ((Len([SN]))<>12));

 

[PHV]

Something like this ?
UPDATE PurchaseOrderScan SET BadScan = Yes
WHERE BadScan = No
AND SN Not Like "[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]"
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BillieBurgess]

This works great, I never thought to specify each character in the expression. Thank you so much. :-D