Select query to extract between word spaces

[CAD Fiend]

Hello,

I have a field called *StreetName* which has the complete street name.
This includes: the number (like 1500), the prefix (like N), the actual
name (like Main), and sometimes two words that are ALSO the name like
(Betty Ford), and then the suffix (like Blvd.). One other thing, there
are also instances where the prefixes and/or suffixes might be ambiguous
(missing the periods) eg. St vs St., or Blvd vs. Blvd.
I would like to extract just the name(s) from the field in a select
query. So for example, if the query encountered a field* entry like:
"1500 N. Betty Ford Blvd." would be only return "Betty Ford" in the
dynaset.

How can I write the query so that it will only select the name(s) and
leave out the other values?

* BTW; what is the correct terminology for this entity, i.e. field
value, field entry, or what?

TIA.

Phil.

 

[Rick B]

You can't. Not based on what you told us. You can do some queries to try
to pull out as much as possible, but there is no way to get even half of
them based on what you told us. We can't simply ignore all numbers, because
you could have "4th street". We can't really pull out directional
indicators because some do have a period and some don't. If we tried
ignoring charachters before a period, then you might miss things like "St.
Anne Street".

There is really not much you can do here to automate the process.

At best, you could pull all the fields that only have two ellements, and
then ignore the first element (street number). The remaining addresses will
most likely have to be done manually.

 

[CAD Fiend]

Rick,

Hmm. Well, I thought I'd at least ask. I know there is a way to do it in Excel,
but there are several steps involved. But if that's what it takes, well then,
that's what it takes.

But thanks, anyway.

Phil.

 

[Rick B]

I don't see how you could possible do it in Excel either.

You can have Excel help you do it, but it is still very manual.

123 E Main St Apt 12
123 Main Street # 12
123 E. Main Number 12
123 East Main Street 12
123 East Main Num. 12
These all have a different number of 'parts'. Some have preiods, some
don't. Some include the words "street" and "apartment" or versions of,
others don't. BUT, they all mean the same thing!

I can't see any way that Excel (or Access) can sort out the above since you
say they are not entered consistently. If I misunderstood your question,
then there may be hope. If Excel can do it, then so can Access (and
probably more easily), so let us know how you would do it in Excel.

 

[CAD Fiend]

See my comments in-line.

I don't see how you could possible do it in Excel either.

You can have Excel help you do it, but it is still very manual.

123 E Main St Apt 12
123 Main Street # 12
123 E. Main Number 12
123 East Main Street 12
123 East Main Num. 12
These all have a different number of 'parts'. Some have preiods, some
don't. Some include the words "street" and "apartment" or versions of,
others don't. BUT, they all mean the same thing!

I can't see any way that Excel (or Access) can sort out the above since you
say they are not entered consistently. If I misunderstood your question,
then there may be hope. If Excel can do it, then so can Access (and
probably more easily), so let us know how you would do it in Excel.

Well, actually, I guess it is pretty manual afterall. What I do is:
a) Copy the entire col. from Access into Excel
b) Save As Text, Tab Delim.
c) Re-open same file, set delimters, then delete unwanted cols.
d) Save as Excel or txt, then import back into Access

Obviously if there is more than 1 word for the street name, then I'll have to
manually reinter it. I know it's not even close to being perfect, but sometimes
it's just good enough for me.