Removing part of a text string

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Access 2000
I have a text field named RoadName that contains the total road name, for
example, "Johnson Road". I have another text field named StreetName that I
would like to just contain the name portion of the RoadName, in this case,
just "Johnson". Is there a way to either remove the Road portion from text
strings in the RoadName field, so that I am just left with "Johnson"? Or
even better, is there a way to automatically populate the new StreetName
field with just the name portion of the original text string (just
"Johnson")? Thanks in advance.
 
Hi.
Is there a way to either remove the Road portion from text
strings in the RoadName field, so that I am just left with "Johnson"?
Click to expand...

Yes. First, make a backup copy of your table, just in case something goes
wrong. Create a new query and switch to the SQL View pane. Paste the
following SQL statement into the pane:

UPDATE MyTable
SET RoadName = Replace(RoadName, " Road", "", 1, 1, 1);

(Change MyTable to the name of your table.) Run the query. All records
containing the word "road" preceded by a space will be removed from this
field. (This prevents the elimination of a partial name such as "Roadside
Court.")
Or
even better, is there a way to automatically populate the new StreetName
field with just the name portion of the original text string (just
"Johnson")?
Click to expand...

Don't "populate" a new field with data that's already stored in another
field in the table, unless you plan to delete that other field. Otherwise,
this would be redundant data that must be updated simultaneously whenever
either of the fields is changed. This is contrary to the rules of proper
table normalization and leads to the loss of data integrity. Instead, use a
query that "calculates" whatever is needed in the original field. This saves
disk space and guarantees that it will always be based upon the value stored
in the original field.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. (Only "Answers" have green
check-marks.) Remember that the best answers are often given to those who
have a history of rewarding the contributors who have taken the time to
answer questions correctly.
 
If your road name ALWAYS ends with the road type, you can use this
query:

UPDATE MyTable
SET RoadName = Left([RoadName],InStrRev([RoadName]," ")-1)

There are also commercial packages that will parse and verify an
address for you. House number, street name, street type, pre
direction, post direction, apt/ste/unit number.

Tom Collins


| Access 2000
| I have a text field named RoadName that contains the total road
name, for
| example, "Johnson Road". I have another text field named StreetName
that I
| would like to just contain the name portion of the RoadName, in this
case,
| just "Johnson". Is there a way to either remove the Road portion
from text
| strings in the RoadName field, so that I am just left with
"Johnson"? Or
| even better, is there a way to automatically populate the new
StreetName
| field with just the name portion of the original text string (just
| "Johnson")? Thanks in advance.
|
 
Enter it with a separate field for each of the smallest units you intend to
use. It's always easy to concatenate strings together but there is no
infallible way to sort it out the way you want once it has been entered as a
concatenated string.

HTH
 
This worked for me (Thank you!) and I was able to isolate the beginning of my
field, but now I want to get the LAST word in my field. How would I adjust
the query to make that happen? Thanks again -
Brenda
 
Back
Top