Please help me split a field into multiple new ones

[HY26]

Hello -

First off, I'm fairly new with using Access. I have a text field that contains a full address and I would like to split that field into 4 parts - street address, town, state, & zip. I can figure out how to split off the zip & state but I cannot figure out how to split street address from town, as many of the towns contain 2 words. I am working on Access 2000. Can anyone offer me some insight on how to do this? Your help is much appreciated!!!

[ajalwaysus]

Please post an example of an address.
Your address field will have to have a consistent character in the field that you will be able to detect with code.

example:

"5487 Masonview Dr., Alberta, MO 51420"

if you do not ALWAYS have those commas, this will not be possible, i don't believe.

-AJ

[HY26]

The example of the address is exactly how they are entered, with the exception of the comma after the street address.
Example:
1234 Orange St. New Haven, CT 06511

The person who enters these usually presses Ctrl-Enter after the street address so that when you're looking at the form, it's formatted like this:
1234 Orange St
New Haven, CT 06511

So, in order to split them I would need to add a comma after the street address for all entries?

[John c]

Is it always a single line address before the town? And is it always ctrl-enter?

[HY26]

From everything I can tell, yes and yes. Is there a way to split it after ctrl-enter?

[missinglinq]

To be honest, I generally stay away from this kind of post, because they usually turn into hairballs! The OP swears that the users always do something a certain way, such as using <Ctrl> + <Enter> after the street address, then after energy is expended and code provided, based on these sworn statements, it's revealed that, well, they don't always do it that way! And the possible variations for these things, like those for complete names, are endless! But here's a code snippet that will split the street address from the city/state/zip, assuming that <Ctrl> + <Enter> is actually used:

Dim CityStateZip as String

If Not IsNull(Me.CompleteAddress) Then
  Me.StreetAddress = Left(Me.CompleteAddress, InStr(Me.CompleteAddress, vbNewLine))
  CityStateZip = Mid(Me.CompleteAddress, InStr(Me.CompleteAddress, vbNewLine))
 End If

You should then be able to use the code you state you already have to split CityStateZip into its components.

Where you use this code depends on where/how you're trying to accomplish this. Are you trying to do it as the complete address is entered, or are you trying to apply it to data that has already been entered.

I'm expecting to be off line for a number of days, but if you have further questions, someone here will be able assist you.

Good Luck and Welcome to Bytes!

Linq ;0)>