How to split content of data in 1 field into 2 fields

[kr85]

I have to run a query in access sql or using query wizard to split the data of field into 2.
The field has data such as 1234 ave willie and has email adresses sucha haha@yahoo.com .I need to create put 1234 ave willie in a new field name St address and haha@yahoo.com in email field.Can some help?
should as wildcard such as like operator etc?

[Seth Schrock]

You could put a percent sign where you want the break as the delimiter. You would then use a combination of the Left() and InStr() functions to get the left side and a combination of the Mid() and InStr() functions to get the right side. For example, lets say your field is named Combination.

Left(Combination, InStr(Combination, "%")-1) AS LeftSide

For the right side you could use

Mid(Combination, InStr(Combination, "%")+1) AS RightSide

If you have spaces around your percent sign, then you would probably want to use the Trim() function around both sets of code so that you don't have spaces throwing any comparisons off.

All that being said, I hope you know that you shouldn't store data with multiple values in the same field per database normalization rules. I understand if you are getting the data from some other program/person, etc. that you would need something like this to separate the data for storage, but I just want to make sure that you understand the issues. For more information about database normalization, read Database Normalization and Table Structures.

[zmbd]

Seth:
this is more like a either a homework question or a conversion from a flat text file that has been dumped in kr85's lap

Also, your solution may be viable in some cases; however, kr85 really hasn't provided enough information to reliably parse the stings.


kr85:
Please consult the FAQ and specific forum sticky threads for further information.

>> Before Posting (VBA or SQL) Code.
>> How to ask "good" questions -- READ BEFORE SUBMITTING A QUESTION!.
>> POSTING_GUIDELI NES: Please Read Carefully Before Posting to a Forum.

Now that you read these, I'm sure that you'll understand:
> Out right requests for full code are against site policy.
> Bytes is not a code nor homework service. If you still don't understand this then please re-read the FAQ and posting guidelines.
> Respectfully, your thread may be removed because asking for someone to provide completed code or a completed project is against site policy - unless you can show what you've already tried.

[kr85]

[Z{Edit - Original Post #4}]
Thanks seth..
We are very close...
again il explain..
I have a field called contact it has either email adress or street adreeses.
When i run my query i need to tell it put all email address in 1 other field called email and all the street address in another field name address.
So the contact field has either an email or street addrees?

[z{Edit - Original Post #5}]

im thinking it should have like "@" and also using instr?but i cant figure it out..

[Z{Origninal Post #6}]

CustomerName                 Contact
Rico                         34 mills ave
James                        [email]bb@yahoo.com[/email]
Dav                          567 rick avenue 
I need query like this:


Customer Name                Email                         Address
Rico                                                     34 mills ave

James                      [email]bb@yahoo.com[/email]

Dav                                                      567 rick avenue

[Z{Original Post#9}]

CustomerName                 Contact
Rico                          34 Mills Avenue
James                         [email]bb@yahoo.com[/email]
Dave                          567 Rick Avenues 


I need query like this:
CustomerName        Email                   Address
Rico                                                        34 mills avenue
James                   [email]bb@yahoo.com[/email]
Dave                                                        567 rick avenue

[Seth Schrock]

Not what I understood from the OP. So just to make sure that I understand your question, the field could contain either a street address OR an email address, but not both. You just want to split the data up into the correct field names.

I have never done anything like this, but I'll give it a shot. Use the IIF() function with the criteria being the InStr() searching for the @ symbol. In one field you would test for it resulting in a 0 and in the second field you would test for it not resulting in a 0. In both cases the true side would result in the combined field. The false side would result in an empty string or "".

[Seth Schrock]

I don't understand your last post.

[Seth Schrock]

The field names changed, but the data is exactly the same. So are you needing two fields with the same data but different names?

[kr85]

[z{original post#14 }]

yes i need it when it execute that query it will take all emails data from contact field and display them in a new field name email then i need it also to pull all mail addresses in a new field name address.

[z{original post#15 }]

right now my table of customer contact is like this:

CustomerName                 Contact
Rico                       34 Mills Avenue
James                       [email]bb@yahoo.com[/email]
Dave                       567 Rick Avenues
Eric                        [email]erics@gmail.com[/email]
Sharon                      222 access drive

[z{original post#16 }]


so i need it to have fields like this when query execute:

CustomerName             Email                   Address
Rico                                             34 mills avenue

Eric                  [email]erics@gmail.com[/email]

[z{original post#17 }]


did you understand what im trying to do
SO im thinking i need to tell it to look for @ for the emails etc.

[Seth Schrock]

Have you tried my suggestion in post #7?

[kr85]

i tried something missing ...could you set me an exampleplz

[Seth Schrock]

Assuming the field name Combination,

IIF(InStr(Combination, "@") = 0, Combination, "") AS Address

If the InStr() function equals 0, that means it didn't find the @ and your data gets put into field titled Address. For the Email field, you would make it not equal to 0 (<> 0) and change the name to Email (or whatever field name you want).

[kr85]

so combination is the field ?

[kr85]

Thanks so much bud ur da man...God bless you