Check for a non exact match - eg serial number with 1 digit differ

Hi All

I need to do a search for serial numbers - I need to find exact match (easy)
but also check for miss types - ie find any numbers that have one or two
digits different to something thats already in the table. The serial number
is 14 digits long and is always letters and numbers.
So if I do a check on:
BZ1A0831002415, then I would want to know if there was already
BZ1A0881002415 or anything similar existing in the table.

Not sure the best way to go about this.

Thanks a lot
Regards
Andrew

.......... thats a very complex query what is your standard for doing
serial numbers ie what is the format of the number or is it just
whatever the person feels like at the time.

Regards
Kelvan

On Mon, 1 Sep 2008 14:57:01 -0700, Andrew P.
wrote:

Hi All

I need to do a search for serial numbers - I need to find exact match (easy)
but also check for miss types - ie find any numbers that have one or two
digits different to something thats already in the table. The serial number
is 14 digits long and is always letters and numbers.
So if I do a check on:
BZ1A0831002415, then I would want to know if there was already
BZ1A0881002415 or anything similar existing in the table.

Not sure the best way to go about this.

Thanks a lot
Regards
Andrew

There's no *easy* way. The best way is to prevent them from being entered in
the first place, e.g. by providing the user with a combo box or other tool to
let them *select* a value known to be correct, rather than forcing them to
type a value. I admit that this isn't always practical - might it be in your
context?

You should also be aware that reversed digits (i.e. BZ1A0881004215) is a very
frequent error type, as is "perseverance" - e.g. seeing 344 and typing 334.

One way to find single-character mismatches is to use fourteen criteria in an
OR:

LIKE "?" & Mid([SN], 2) OR LIKE (Left([SN], 1) & "?" & Mid([SN], 3) OR LIKE
(Left([SN], 3) & "?" & Mid([SN], 4) et cetera et cetera

but it's going to be really slow and inefficient.
--

John W. Vinson [MVP]

the combo box wouldnt work john because it seems he is trying to
remove the duplication not select one that is currentaly there

i am more concerned about if that is actually a legitimate serial
number

would
BZ1A0881002415

ever be an legitimate serial number or is

BZ1A0831002415

the legit one

or can they both be legit serial numbers

or can they both me non legit numbers

Regards
Kelvan

They all can be legitimate - the problem is that our group enter the info on
a form, and the person filling the original form can sometimes *accidently*
enter a slightly different number if the first serial number has been
rejected before. So the aim is to pick up on anything that has been rejected
previously.

"Lord Kelvan" wrote:

the combo box wouldnt work john because it seems he is trying to
remove the duplication not select one that is currentaly there

i am more concerned about if that is actually a legitimate serial
number

would
BZ1A0881002415

ever be an legitimate serial number or is

BZ1A0831002415

the legit one

or can they both be legit serial numbers

or can they both me non legit numbers

Regards
Kelvan

On Mon, 1 Sep 2008 17:07:01 -0700, Andrew P.
wrote:

They all can be legitimate - the problem is that our group enter the info on
a form, and the person filling the original form can sometimes *accidently*
enter a slightly different number if the first serial number has been
rejected before. So the aim is to pick up on anything that has been rejected
previously.

What deterimines that a value has been rejected? Do you have a table of
rejected ID's? I presume this is a paper form (which adds the extra risks of
handwriting like mine, or misreading a 7 for a 1, or...)?

Again: does the computer have a table of valid serial numbers, or are these
coming into the computer de novo?
--

John W. Vinson [MVP]

mmm as john said why is it rejected do you have a formula that defines
the serial number or is it a manual rejection if it is then that value
needs to be stotred in a table so when a user enter it it can be read
out and then the program can say no to the user

Regards
Kelvan

Thanks for your help John & Kelvan. The text I went with is:
Like Left([Forms]![ClaimEntry]![Serial],12) & "#" &
Right([Forms]![ClaimEntry]![Serial],1)........
Which is working quite well now. As for the criteria of rejections, its in
the tables already, so Ill just put it in the criteria. The only problem Im
still finding is a way to check if the query comes back positive. Ive tried
assinging to a button a simple message box using the builder using condition:
Count([SerialCheck]![Entry])0
just to find if the query returns anything, but I keep getting complaints
from Access like "The object doesnt contain the Automation object
'SerialCheck'". What am I doing wrong here?
Thanks
Andrew

"Lord Kelvan" wrote:

mmm as john said why is it rejected do you have a formula that defines
the serial number or is it a manual rejection if it is then that value
needs to be stotred in a table so when a user enter it it can be read
out and then the program can say no to the user

Regards
Kelvan

ummm dont use a ! in that use a .


Count([SerialCheck].[Entry])0

and that shoudl resolve that problem

if anything you should remove the users ability to enter serial
numbers and have the computer generate them

Regards
Kelvan

On Mon, 1 Sep 2008 19:01:09 -0700, Andrew P.
wrote:

The only problem Im
still finding is a way to check if the query comes back positive. Ive tried
assinging to a button a simple message box using the builder using condition:
Count([SerialCheck]![Entry])0

I'd suggest not using a separate query at all, then:

If IsNull(Dlookup("[Entry]", "[SerialCheck]", "your criteria") Then
the pattern was not found
Else
it was
End If
--

John W. Vinson [MVP]