I have a field in an Access record data-set that contain the addresses of businesses. The table contains 7,000 records.
|646 RACOON RDG SCOTTSVILLE VA 24590 |
|1120 KINGSWAY RD AFTON VA 22920 |
|3606 MILLINGTON RD FREE UNION VA 22940 |
|248 REAS FORD RD EARLYSVILLE VA 22936 |
|2684 CHAPEL SPRINGS LN FREE UNION VA 22940 |
|2030 AVON CT UNIT 8 CHARLOTTESVILLE VA 22902 |
|1667 BAILEYS RETREAT RD CHARLOTTESVILLE VA 22901 |
HseNum|Street Name |Suite |City |State |Zip
646 |RACOON RDG | |SCOTTSVILLE |VA |24590
1120 |KINGSWAY RD | |AFTON |VA |22920
3606 |MILLINGTON RD | |FREE UNION |VA |22940
248 |REAS FORD RD | |EARLYSVILLE |VA |22936
2684 |CHAPEL SPRINGS LN | |FREE UNION |VA |22940
2030 |AVON CT |UNIT 8 |CHARLOTTESVILLE |VA |22902
1667 |BAILEYS RETREAT RD| |CHARLOTTESVILLE |VA |22901
I have uploaded two .txt files. One is of the example above and the other is a 100 record data-set.
I have been successful using Left() to get the HseNum and InStrRev() to get the State and Zip but the records that have a two-part City name and/or a two-part Street name has me stumped. I have also checked the knowledge base but could not find a satisfactory solution.
I am getting near a deadline and need help to move forward. Thanks in advance!!