Split data in field

[Shawn Johnson]

Hello,

I have a database with customers and their addresses. In the streetname
field the names have been entered including the street type (ex. Main ST) I
would like to split this field so as to have the street name in one field
and the street type in another. I should mention that I do have another
table that has all the different street types (RD, ST, HWY etc)

I am fairly new to Access so please simplify your answers for me.

Shawn J

 

[John Vinson]

Hello,

I have a database with customers and their addresses. In the streetname
field the names have been entered including the street type (ex. Main ST) I
would like to split this field so as to have the street name in one field
and the street type in another. I should mention that I do have another
table that has all the different street types (RD, ST, HWY etc)

One problem you'll have is inconsistancy: I'm sure you'll have entries
like "1st Ave" and "1st Av" and "1st Avenue" (and even "1st Aveune"!)

Another problem is two-word streets; I live near Old Fort Boise Rd.,
which is often written just "Old Fort Boise". The street type is, of
course, Rd or Road, not "Boise" - but parsing a field entry will not
reveal that fact!

Yet another is that you will (not may, WILL, miracles being the only
out) have multiple address entries without a street type entered.

That said... for the SIMPLE cases, you can change your table design to
add a StreetType field. Create an Update query updating it to

Trim(Right([Streetname], InStrRev([Streetname], " "))

This will copy the last blank-delimited "word" of the streetname field
into StreetType. You'll need to manually edit this, I suspect.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Brendan Reynolds]

Another exception to the rule that would be common at least in this part of
the world is a 'modifier' after the street type: Some St Nth, Some St West,
Some St Upper, Some St Lower, etc.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Hello,

I have a database with customers and their addresses. In the streetname
field the names have been entered including the street type (ex. Main ST)
I
would like to split this field so as to have the street name in one field
and the street type in another. I should mention that I do have another
table that has all the different street types (RD, ST, HWY etc)

One problem you'll have is inconsistancy: I'm sure you'll have entries
like "1st Ave" and "1st Av" and "1st Avenue" (and even "1st Aveune"!)

Another problem is two-word streets; I live near Old Fort Boise Rd.,
which is often written just "Old Fort Boise". The street type is, of
course, Rd or Road, not "Boise" - but parsing a field entry will not
reveal that fact!

Yet another is that you will (not may, WILL, miracles being the only
out) have multiple address entries without a street type entered.

That said... for the SIMPLE cases, you can change your table design to
add a StreetType field. Create an Update query updating it to

Trim(Right([Streetname], InStrRev([Streetname], " "))

This will copy the last blank-delimited "word" of the streetname field
into StreetType. You'll need to manually edit this, I suspect.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps