Hi,

I am new to programming in Access and therefore need some assistance, as my knowledge is very limited.

I have a number of Model numbers that due to the nature of how they are provided could have numerous variations. I need to know if I can write code that will automatically produce all the possible variations, for instance:

Model No: B1322N1GB

Because of the way customers write their information the B could be mistaken for the numerical character "8"

The numerical character "1" could be mistaken for the alphabetical characters "I" [Uppercase] or "l" [Lowercase L]

The numerical character "2" could be mistaken for the alphabetical character "Z"

The alphabetical character "N" could be mistaken for the alphabetical character "H"

Also as some of the model numbers include the alphabetical character "D" this could be mistaken for the alphabetical character "O" or numerical character "0" [zero]

I need to be able to write code that could produce the model number with all these possible variations.

This would give me a complete listing of all those possibilities and I could then produce a process that would pick these out and then allocate the correct model number.

Any assistance with this would be most appreciated

FAB

John Lee

All I can say is, OUCH.

Well, folks who know me doubt that's all I can say, but...

The problem is that you need to do some VBA code to pick apart the string one character at a time and for each character that has a visual near match, run a progression of them. However, this is a deep issue in looping.

You need to realize that in theory you will get a number of options equal to the factorial (or worse) of the number of characters that have these alternative forms.

Lets take a simple case. BI111NG1B - written here as bee eye one one one en gee one bee.

So you have two options with B - itself or 8.

You have three options each with the next four characters.

You have two options with the N.

A sloppy G might look like a 6, so let's put two more options there. And the last B has two options.

So this is 2 x 3 x 3 x 3 x 3 x 2 x 2 x 3 x 2 combinations of these visual errors. That is (2^4) * (3^5) = 16 * 243 = 3,888 possible combinations for that one serial number.

Are you ready to do something with that many combinations? That will take a while to generate and a longer while to compare.

I would step back from this beast to see if you could make a different approach. Do you know the absolute form of the model number? Is it invariant across the product line? If so, take the OTHER approach.

The serial number you showed me is alpha alpha digit digit digit alpha alpha digit alpha.

So let's look at the possibilities again. You were expecting BI111NG1B but let's say you got 81lllH6I8. That is eight one little-l (three times) aitch six eye eight. Look at the first character. Is it alpha? No, it is a digit. Does it have a possible visible match? Yes - substitute that. Look at the next one. Is it alpha? No. Has a visual match? Yes. Put in the alpha that is most likely - the uppercase I. Use similar logic character by character throughout.

Of course, if your model numbers are not well-behaved, this won't work worth a toot. But if they are predictable, you can do a most-likely number on each item individually. If you have options - like the N could have really been an H in some cases, you have a few combinations left to consider. But where the structure of the model number is exactly predictable, you only need to consider the option that makes sense.

OK, so how do you do this? In VBA, you can write code that scans the model designator one character at a time using the Mid function (q.v. in Access help). You can build a SELECT statement based on what you see and what you expected to see.

You would test the single character for being the right type and if it is wrong, have a SELECT ladder to substitute it. Using loose pseudo-code, this might start like:


...
x <-- next character from input string
y <-- type of x (digit, alpha)
z <-- expected type of x (digit, alpha)
if y = z then
w = x
else
select x
case '8'
w = 'B'
case 'B'
w = '8'
case 'I'
w = '1'
case 'l' (lower-case l)
w = '1'
etc etc etc
end select
end if
output string <-- output string & w


If you run into the case where you have the character of the right type but there is a substitution available (like the 'N' vs 'H' case), that gets trickier. But the solution is to be ready to eliminate impossible cases. Like, if they wrote H but no model number has H in that position, you might do a different type of substitution.

I guess the short way to say this is if you know the correct form of the model number then don't make your life harder with combinatorial math numbering in the thousands of possible combinations. Reduce the problem another way if at all possible.

Hi Doc Man,

Thanks for your insight, your right, I will need to review what I am trying to achieve and if this route is really the best approach.

Your assistance is most appreciated.

John Lee