Split a field

[Bryan]

I have alot address that I want to split about 7,000. I want to take the
house number and send it to text1 and the street to text2. What is the code
for this. I used to have it but that was years ago and where should I put
the code. basically at the first space I would like to split the data and
send part of it one way and the other part another way. I don't need to save
it in a table just need it split so I can send it to a formula.

Also. Is there a way to make my email fields a mail to: hyperlink? I tried
several ways and get www.mailtoXXXXXX I need to get rid of the www.

 

[Sprinks]

Bryan,

Assuming your data follows the convention:

{Series of # and characters representing house number}{Space}{Rest of address}

Text1 = Left([Address],InStr([Address]," ") -1)
Text2 = Mid([Address],InStr([Address]," ")+1)

The best way to do it is to add two new fields to your table definition, and
use the expressions to populate them using an insert query. *After* you have
verified that it worked, you can delete the old field.

Hope that helps.
Sprinks

 

[Bryan]

That worked great! What if I wanted to continue to break down the names of
the street into diffrent text boxes.

How could I continue the expression to have East North Shore Dr end up

Text3 East
Text4 North
Text5 Shore
Text6 Dr

What I want to do is create a hyper link to whitepages.com. I am going to
take the information in Text1 - Text6 and put it into the url formula for a
phone number search.

Bryan,

Assuming your data follows the convention:

{Series of # and characters representing house number}{Space}{Rest of address}

Text1 = Left([Address],InStr([Address]," ") -1)
Text2 = Mid([Address],InStr([Address]," ")+1)

The best way to do it is to add two new fields to your table definition, and
use the expressions to populate them using an insert query. *After* you have
verified that it worked, you can delete the old field.

Hope that helps.
Sprinks

I have alot address that I want to split about 7,000. I want to take the
house number and send it to text1 and the street to text2. What is the code
for this. I used to have it but that was years ago and where should I put
the code. basically at the first space I would like to split the data and
send part of it one way and the other part another way. I don't need to save
it in a table just need it split so I can send it to a formula.

Also. Is there a way to make my email fields a mail to: hyperlink? I tried
several ways and get www.mailtoXXXXXX I need to get rid of the www.

 

[Sprinks]

Bryan,

Sorry for not responding sooner.

Use multiple iterations, adding an IIf clause to check if it's already at
its final state. Also, although I think Access deletes any trailing spaces
from fields automatically, you can be sure you don't have any by calling the
Trim statement. Also, if InStr does not find a space, set the field to its
current value. So the next iteration would be:

Text2 = IIf(InStr(Trim([Text2])," ")<>0,Left([Text2],InStr(Trim([Text2]),"
")-1),[Text2])

Text3 = IIf(InStr(Trim([Text2])," ")<>0,Mid([Text2],InStr(Trim([Text2]),"
")+1));

Then increment Text2 to Text3 and Text3 to Text4 and continue until you've
parsed all addresses fully.

Hope that helps.
Sprinks

That worked great! What if I wanted to continue to break down the names of
the street into diffrent text boxes.

How could I continue the expression to have East North Shore Dr end up

Text3 East
Text4 North
Text5 Shore
Text6 Dr

What I want to do is create a hyper link to whitepages.com. I am going to
take the information in Text1 - Text6 and put it into the url formula for a
phone number search.

Bryan,

Assuming your data follows the convention:

{Series of # and characters representing house number}{Space}{Rest of address}

Text1 = Left([Address],InStr([Address]," ") -1)
Text2 = Mid([Address],InStr([Address]," ")+1)

The best way to do it is to add two new fields to your table definition, and
use the expressions to populate them using an insert query. *After* you have
verified that it worked, you can delete the old field.

Hope that helps.
Sprinks

I have alot address that I want to split about 7,000. I want to take the
house number and send it to text1 and the street to text2. What is the code
for this. I used to have it but that was years ago and where should I put
the code. basically at the first space I would like to split the data and
send part of it one way and the other part another way. I don't need to save
it in a table just need it split so I can send it to a formula.

Also. Is there a way to make my email fields a mail to: hyperlink? I tried
several ways and get www.mailtoXXXXXX I need to get rid of the www.