Need a procedure in VBA (ms access)

[catib01]

Hi!
I have a table in an MS Access database with a field which keeps the address. The Address was not entered in a consistent way. ex: "24 strName, city..." or "city, zip, strName". There are many records in the table, more then 100k
I need a procedure/function/module to extract the strName from the field.Can it be done usind Instr? If so how?

Any help will be appreciated.

[ADezii]

It would be helpful if you could possibly list variations on how the same address would be entered. No matter what Procedure we come up with, it will never be foolproof.

[Oralloy]

In this, I agree with ADezii - you will never be able to match all patterns for names correctly, but you can solve for all of your data.

I've done this before, and it took quite a few pattern matches to get through a large (ca. 100,000 records) database. I did not fully check every result, but my method left me with high confidence. I started by formulating a solution and testing it. Then, I would spot check the answers, and the failures, before adding additional capability. Here is a pseudo algorithm:

1. Build a potential solution
2. Run test
3. Check result for positive answers, if there is a problem, rework the last solution so that the selection is better/smarter.
4. Check the failures, dream up an enhancement to the selection, and add it to the soltuion.
5. Restart at test (#2).

The structure of the code that I ended up with was:

If Match(input to pattern1) Then
  Select fields and store. 
Else If Match(input to pattern2) Then
  Select fields and store. 
Else If Match(input to pattern3) Then
  Select fields and store.
Else
  ''--match failure
  Report failure.
End If
 

Happy Hunting,
Oralloy

[catib01]

Oralloy,
I am not sure if I get this. Can you send me the 'If Match(input to pattern1) Then
Select fields and store'. I see as it is a function which takes argument the field.
Example:
? CHURCH HILL MEADOWS BLVD
BEECHWOOD RETIREMENT 1500 RATHBURN RD E
In the example above I need to extract just the street. I have a list for the correct streets that suppose to be correct and want to extract the street only from the Address field then once I have the streets separately in a new field to match against the correct streets.
I hope to get some help and will be truly appreciated