Splitting City, State, and Zip 8

[alr0]

Hi All,

I am trying to write a city, state, zip splitter. I have a lot of the logic worked out once the field is split but sometimes that are many spaces internally, plus I need to look for commas as separaters also.

The code could repeatedly replace double spaces with single spaces or look at each character in the string with tons of Ifs but neither seems like an efficient solution.

Has anyone found a good way to accomplish this using Access 03?

Thanks,

alr

_____________________________________
There is no rule more invariable than that we are paid for our suspicions by finding out what we expected.

Henry David Thoreau

 

[alr0]

Thanks to all of you for your input.

We had an emergency today so I did not spend much time on this but it is number 2 on my list for next year and it will take a little time to work through all the suggestions and the code that Tarnish was kind enough to send. I will let you know how it turns out.

Hi JoeReed

The code name for this new program is LeftFielder because we too have been using the product you recommended for years. It is not bad but we still have to fix and move records by hand probably because our source data is so bad.

Have a great holiday everyone!

Thanks again,

alr

_____________________________________
There is no rule more invariable than that we are paid for our suspicions by finding out what we expected.

Henry David Thoreau

 

[SkipVought]

Costello: Out left fielder's a girl, you know.

Abbott: Is that so?

Costello: No! It's Sue!

Abbott: Sue who?

Costello: That's right! Our First Basemen's wife!

(actually it was Hudson & Landry -- not Abbott & Constello)



Skip,
[sub]
[red][/red]
[/sub]

 

[missinglinq]

And just in case anyone out there's not seen one, the best $25 USD I ever spent was on a table that had Zip, City, State and Area Code. User puts in the Zip and all the other fills in! No mistakes!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[alr0]

Hi All,

Thanks to Tarnish and Allen Browne we have a new City, State, Zip splitter that does a better job for our needs than Right Fielder and does not require exporting data to DBFs. It is a little slower running but that time is more than made up in other ways.

When data is returned from the code Tarnish supplied, all punctuation is replaced with spaces, multiple spaces are deleted, and it is parsed into an array with each word separated. I added another array and stored the length of each component and evaluated from the right:

Last piece length 2:Copy to state and concatenate the remainder to city.

Last pair 2 and 5 numbers: 2 to state, 5 to zip, and remainder to city.

Last 3 components 2, 5 nos, 4 nos: same with zip4.

Last pair 2 and 9 numbers: 2 to state, 5 to zip, 4 to zip4, and remainder to city.

I had a test to check the 2 letter state abbreviations in a lookup table but it took too long. We may get some country codes mixed in but if it is a problem they can be cleaned up later with a query. I do have more refining and testing to do but it is quick and accurate. I also modified the code Tarnish supplied to be a bit less general and faster but the code works great.

I am always a bit nervous about not taking Skips excellent advice but this seems to clean up the mess automatically and better than Right Fielder did.

Thanks again to all that offered their input,

alr


_____________________________________
There is no rule more invariable than that we are paid for our suspicions by finding out what we expected.

Henry David Thoreau

 

[MichaelRed]

hmmmmmmmmmmmmmmmmmmmmmmmm ... mmmmmmmmmmmmmmmmmm,

It would SEEM like with the substantial help you received here, and the eventual good results you would post the actual code of your results.

Since this is a COMMON and DIFFICULT task, it might be worthy of a FAQ, and at thr very least, posting your finished bersion would provide others with a starting point for their own attempts and quite possibly additional suggestions for improvement in your procedure(s).




MichaelRed