replace text in a string

[pedro]

I have an address field in a database and I would like to replace
abbreviations (Rd, St, Ave etc) with the full text for these abbreviations
(eg Road, Street, Avenue) on the after update event.

I could use the replace function in VBA;
replace([address]), "St","Street")
however this will replace all instances of "St" with "Street" including the
name of the street.
Is there a way to locate the the "St" or "Ave" at the end of the string and
then use the replace function?

thanks

 

[Allen Browne]

You could use something like this:
=IIf([address] Like "* St", replace([address]), "St","Street"), [address])

 

[Douglas J. Steele]

That's still not going to work, Allen. You'd still end up with 123 First St
getting changed to 123 FirStreet Street (and 345 Third Rd getting changed to
345 ThiRoad Road)

=IIf([address] Like "* St", [address] & "reet", [address])

would work, but obviously that wouldn't work for Rd. For that, you could use

=IIf([address] Like "* Rd", Left([address], Len([address]) - 2) & "Road",
[address])

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

You could use something like this:
=IIf([address] Like "* St", replace([address]), "St","Street"), [address])


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have an address field in a database and I would like to replace
abbreviations (Rd, St, Ave etc) with the full text for these
abbreviations
(eg Road, Street, Avenue) on the after update event.

I could use the replace function in VBA;
replace([address]), "St","Street")
however this will replace all instances of "St" with "Street" including
the
name of the street.
Is there a way to locate the the "St" or "Ave" at the end of the string
and
then use the replace function?

thanks

 

[Mike Painter]

I have an address field in a database and I would like to replace
abbreviations (Rd, St, Ave etc) with the full text for these
abbreviations (eg Road, Street, Avenue) on the after update event.

I could use the replace function in VBA;
replace([address]), "St","Street")
however this will replace all instances of "St" with "Street"
including the name of the street.
Is there a way to locate the the "St" or "Ave" at the end of the
string and then use the replace function?

thanks

Use the Split function to separate the address then change it.

If you are doing this for mailing then you should be changing street to ST
which is what teh post office wants.
If you do this a lot with a big table then you might want to look into
Address normalization (Post office, not relational) The USPS has routines
for this.
If you don't do it a lot you can probably find somebody to do it for you.

 

[pedro]

Thanks for that.

Would it be possible to put this in an "if-then-else" statement?

Otherwise there are potential conflicts when the string updates - eg if Lord
St is entered in the field, then the text is cleared when the after update is
fired.

regards

That's still not going to work, Allen. You'd still end up with 123 First St
getting changed to 123 FirStreet Street (and 345 Third Rd getting changed to
345 ThiRoad Road)

=IIf([address] Like "* St", [address] & "reet", [address])

would work, but obviously that wouldn't work for Rd. For that, you could use

=IIf([address] Like "* Rd", Left([address], Len([address]) - 2) & "Road",
[address])

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

You could use something like this:
=IIf([address] Like "* St", replace([address]), "St","Street"), [address])


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have an address field in a database and I would like to replace
abbreviations (Rd, St, Ave etc) with the full text for these
abbreviations
(eg Road, Street, Avenue) on the after update event.

I could use the replace function in VBA;
replace([address]), "St","Street")
however this will replace all instances of "St" with "Street" including
the
name of the street.
Is there a way to locate the the "St" or "Ave" at the end of the string
and
then use the replace function?

thanks

 

[Douglas J. Steele]

Sorry, I don't understand what you're getting at. Why would the text be
cleared in the AfterUpdate event?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for that.

Would it be possible to put this in an "if-then-else" statement?

Otherwise there are potential conflicts when the string updates - eg if
Lord
St is entered in the field, then the text is cleared when the after update
is
fired.

regards

That's still not going to work, Allen. You'd still end up with 123 First
St
getting changed to 123 FirStreet Street (and 345 Third Rd getting changed
to
345 ThiRoad Road)

=IIf([address] Like "* St", [address] & "reet", [address])

would work, but obviously that wouldn't work for Rd. For that, you could
use

=IIf([address] Like "* Rd", Left([address], Len([address]) - 2) & "Road",
[address])

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

You could use something like this:
=IIf([address] Like "* St", replace([address]), "St","Street"),
[address])


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


I have an address field in a database and I would like to replace
abbreviations (Rd, St, Ave etc) with the full text for these
abbreviations
(eg Road, Street, Avenue) on the after update event.

I could use the replace function in VBA;
replace([address]), "St","Street")
however this will replace all instances of "St" with "Street"
including
the
name of the street.
Is there a way to locate the the "St" or "Ave" at the end of the
string
and
then use the replace function?

thanks

 

[pedro]

For example, if I enter Lord St into the address field and hit enter, rather
than updating to Lord Street, the field is cleared.

I have followed your suggestion and placed the following code on the after
update event

address = IIf([address] Like "* St", [address] & "reet", [address])
address = IIf([address] Like "* Rd", Left([address], Len([address]) - 2) &
"Road",
[address])

Can this be transformed into an if-then-else statement?

Sorry, I don't understand what you're getting at. Why would the text be
cleared in the AfterUpdate event?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for that.

Would it be possible to put this in an "if-then-else" statement?

Otherwise there are potential conflicts when the string updates - eg if
Lord
St is entered in the field, then the text is cleared when the after update
is
fired.

regards

That's still not going to work, Allen. You'd still end up with 123 First
St
getting changed to 123 FirStreet Street (and 345 Third Rd getting changed
to
345 ThiRoad Road)

=IIf([address] Like "* St", [address] & "reet", [address])

would work, but obviously that wouldn't work for Rd. For that, you could
use

=IIf([address] Like "* Rd", Left([address], Len([address]) - 2) & "Road",
[address])

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


You could use something like this:
=IIf([address] Like "* St", replace([address]), "St","Street"),
[address])


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


I have an address field in a database and I would like to replace
abbreviations (Rd, St, Ave etc) with the full text for these
abbreviations
(eg Road, Street, Avenue) on the after update event.

I could use the replace function in VBA;
replace([address]), "St","Street")
however this will replace all instances of "St" with "Street"
including
the
name of the street.
Is there a way to locate the the "St" or "Ave" at the end of the
string
and
then use the replace function?

thanks