Editing text fields in Access 2000

[JASON.BUCK]

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.

[Phil Stanton]

Try as the controlsource of StreetName

IIf(Instr(RoadName), " " > 0, Left(RoadName Instr(RoadName, " ")-1),
RoadName)
This looks for the first space in the RoadName and sets the Street name to
the left of RoadName how ever many letters there are before the first space.
If there is no space, you get the whole word.

I have my doubts that it will always work. You could change the " " to "
Road" if you want to.
Note the IIf is not a misprint

HTH

Phil
<JA********@CO.COLUMBIA.WI.US> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...

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.

[Darryl Kerkeslager]

Not perfectly accurately, but something like this (This is changed from
similar code I use, so it might not be exactly right):

Public Function ParseRoad(roadName As String)
Dim i As Integer
Dim s As String
Const strList = "street avenue boulevard drive road way court terrace
lane highway place"

s = Trim$(roadName)
i = InStrRev(s, " ")
If i > 0 Then
s1 = Mid$(s, i + 1)
If InStr(1, strList, s1, vbTextCompare) > 0 Then
ParseRoad = Mid$(s, i - 1)
Else
ParseRoad = s
End If
End Function

This ought to handle 99% of streets

--
Darryl Kerkeslager

Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.
See www.adcritic.com/interactive/view.php?id=5927

<JA********@CO.COLUMBIA.WI.US> wrote

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.