Number Prefix Lookup Table in MDB

[John R]

Hi all,

I'm trying to get my VB6 app to look up prefixes for a phone number out of
an MDB file along with an associated price etc.

For example the phone number could be 9802xxxx, and the MDB file will have
the record 9802 with an associated price.

I am used to connecting to databases via ADO, but my problem is that the MDB
file may contain prefixes from 1 to 5 chars in length and i'm not sure how
to get VB to find the best match.

I'm assuming it can be done with a very large nested IF statement, is there
a better way?

Cheers,
John

[Bob Butler]

"John R" <jo*********@SPAMhotmail.com> wrote in message news:<3f***********************@news.optusnet.com. au>...

Hi all,

I'm trying to get my VB6 app to look up prefixes for a phone number out of
an MDB file along with an associated price etc.

For example the phone number could be 9802xxxx, and the MDB file will have
the record 9802 with an associated price.

I am used to connecting to databases via ADO, but my problem is that the MDB
file may contain prefixes from 1 to 5 chars in length and i'm not sure how
to get VB to find the best match.

he exmaple you give matches the first 4 digits of an 8-character code
against a field that is an exact match. That seems like a simple
query. You then say it can be 1-5 characters and you want the "best
match" but don't explain what the possible matches are or which would
be better than others... can you elaborate with some more examples
include good/better/best matches?

[John R]

Thanks for the reply Bob, let me see if i can explain it a bit better.

I am trying to parse an output file from a PABX phone system. I have an MDB
file with aprox 200 prefixes which rance from 1-5 chars in length (eg:
country codes, national & local telephone codes).

as an example, an international number dialled might be: 001161290000000,
and this would correspond to the record '0011' (int. call prefix) in the
MDB file. yet a local number would be just 90000000, and correspond to the
record '9' as long as there is no record '90' etc. So you can see my dilemma
with length of strings.

It will always be an exact match, but i may have the strings "9" "90" and
"901", the number "9058321" would have to be matched to "90" in the
database and "921345" would have to match just "9".

I was thinking of a large IF statement starting with LEFT(string,5), if it
finds no match move on to LEFT,4 and etc all the way to 1.

Is there a better way? if not whats the best way of organising an if
statement that large?

Cheers,
John R.

"Bob Butler" <bu*******@earthlink.net> wrote in message
news:fa*************************@posting.google.co m...

he exmaple you give matches the first 4 digits of an 8-character code
against a field that is an exact match. That seems like a simple
query. You then say it can be 1-5 characters and you want the "best
match" but don't explain what the possible matches are or which would
be better than others... can you elaborate with some more examples
include good/better/best matches?

[J French]

On Thu, 4 Dec 2003 23:57:34 +1100, "John R"
<jo*********@SPAMhotmail.com> wrote:

Thanks for the reply Bob, let me see if i can explain it a bit better.

I am trying to parse an output file from a PABX phone system. I have an MDB
file with aprox 200 prefixes which rance from 1-5 chars in length (eg:
country codes, national & local telephone codes).

as an example, an international number dialled might be: 001161290000000,
and this would correspond to the record '0011' (int. call prefix) in the
MDB file. yet a local number would be just 90000000, and correspond to the
record '9' as long as there is no record '90' etc. So you can see my dilemma
with length of strings.

It will always be an exact match, but i may have the strings "9" "90" and
"901", the number "9058321" would have to be matched to "90" in the
database and "921345" would have to match just "9".

I was thinking of a large IF statement starting with LEFT(string,5), if it
finds no match move on to LEFT,4 and etc all the way to 1.

Is there a better way? if not whats the best way of organising an if
statement that large?

If you only have 200 prefixes to deal with, then I would pull the lot
out into an Array
- pound them into a standard format
- and do a Binary Chop

If I understand your problem correctly, you need to re-format the data
and the search key (using the same routine)

At which point the problem disappears
eg:
A single '9'
would find its best match if it were looking for '9' + Chr$(32)

[John R]

Can you explain this 'routine' a little better?

Cheers,
John R

If you only have 200 prefixes to deal with, then I would pull the lot
out into an Array
- pound them into a standard format
- and do a Binary Chop

If I understand your problem correctly, you need to re-format the data
and the search key (using the same routine)

At which point the problem disappears
eg:
A single '9'
would find its best match if it were looking for '9' + Chr$(32)

[Steve Gerrard]

"John R" <jo*********@SPAMhotmail.com> wrote in message
news:3f***********************@news.optusnet.com.a u...

Can you explain this 'routine' a little better?

Cheers,
John R

If you only have 200 prefixes to deal with, then I would pull the lot out into an Array
- pound them into a standard format
- and do a Binary Chop

If I understand your problem correctly, you need to re-format the data and the search key (using the same routine)

At which point the problem disappears
eg:
A single '9'
would find its best match if it were looking for '9' + Chr$(32)

1. Load the 200 prefixes into a recordset, and sort them by Len(Prefix)
DESC, so the longest are first.
2. Copy the records into an array.
3. Run through the phone number records, and for each one, scan the
array for a match.
4. Store the correct prefix in each phone number record, so you don't
have to do it again.
5. The loop for one record might look something like
For n =1 to 200
If (Left(rs("PhoneNumber"),Len(arrayPrefix(n)) =
arrayPrefix(n) Then
rs("Prefix") = arrayPrefix(n)
rs.Update
Exit For
next n
6. Make the array and matching function available for new and edited
records, so they get their prefix assigned when they are entered or
updated.

[John R]

Thats a good way of doing it! Thankyou all.

If you only have 200 prefixes to deal with, then I would pull the lot out into an Array
- pound them into a standard format
- and do a Binary Chop

If I understand your problem correctly, you need to re-format the data and the search key (using the same routine)

At which point the problem disappears
eg:
A single '9'
would find its best match if it were looking for '9' + Chr$(32)

1. Load the 200 prefixes into a recordset, and sort them by Len(Prefix)
DESC, so the longest are first.
2. Copy the records into an array.
3. Run through the phone number records, and for each one, scan the
array for a match.
4. Store the correct prefix in each phone number record, so you don't
have to do it again.
5. The loop for one record might look something like
For n =1 to 200
If (Left(rs("PhoneNumber"),Len(arrayPrefix(n)) =
arrayPrefix(n) Then
rs("Prefix") = arrayPrefix(n)
rs.Update
Exit For
next n
6. Make the array and matching function available for new and edited
records, so they get their prefix assigned when they are entered or
updated.