By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,862 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

Please help me split a field into multiple new ones

P: 3
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!!!
Aug 26 '09 #1

✓ answered by 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:
Expand|Select|Wrap|Line Numbers
  1. Dim CityStateZip as String
  2.  
  3. If Not IsNull(Me.CompleteAddress) Then
  4.   Me.StreetAddress = Left(Me.CompleteAddress, InStr(Me.CompleteAddress, vbNewLine))
  5.   CityStateZip = Mid(Me.CompleteAddress, InStr(Me.CompleteAddress, vbNewLine))
  6.  End If
  7.  
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)>

Share this Question
Share on Google+
5 Replies


Expert 100+
P: 266
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
Aug 26 '09 #2

P: 3
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?
Aug 26 '09 #3

P: 4
Is it always a single line address before the town? And is it always ctrl-enter?
Aug 26 '09 #4

P: 3
From everything I can tell, yes and yes. Is there a way to split it after ctrl-enter?
Aug 26 '09 #5

missinglinq
Expert 2.5K+
P: 3,532
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:
Expand|Select|Wrap|Line Numbers
  1. Dim CityStateZip as String
  2.  
  3. If Not IsNull(Me.CompleteAddress) Then
  4.   Me.StreetAddress = Left(Me.CompleteAddress, InStr(Me.CompleteAddress, vbNewLine))
  5.   CityStateZip = Mid(Me.CompleteAddress, InStr(Me.CompleteAddress, vbNewLine))
  6.  End If
  7.  
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)>
Aug 27 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.