Query Help

[William via AccessMonster.com]

I have an access application that tracks clothing in and out of a laundering
process for a cleaner.
The Main table I have contains SeqNo (auto id Pk) ScannedItem(Text) TimeIn
(Date time)TimeOut ComputerNo(Text) and so on....
We have been using the homegrown system about 2 -1/2 Years now and every
other month when the garments are being scanned-in an error will emerge in
our data. We have error checking going on within our scan-in forms but so how
they are getting (/) or (Any different letters or words) in the ScannedItem
field. How can I build a query that will point out these errors so the
manager of the laundry can delete them easily? They affect the system when
they go to sort out the garments to the individuals. The number scanned is
between 5-12 numbers which identifies the garment and the person associated
with it. We run around 30,000 – 50,000 records a month.

 

[John Spencer]

If scannedItem is supposed to contain numbers only and no other type of
characters, then you could use criteria like the following

WHERE ScannedItems Like "*[!0-9]*"

That should find any record where scanneditem field contains anything but
number characters.

If you want something more complex, you need to tell us what the good values
would look like. For instance, you might say the field always starts with A,
B, or C and always ends with D,E, or F and is always 9 characters long - the
seven middle characters are all numbers.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County