a search and replace query

[Southern at Heart]

I need a simple update query to find the & sign and replace it with {and}
....the & sign gives trouble on down the road. Can someone please show me how
to do this in a query? I know it should be hard, but my brain seems froze!
thanks.
oh, my table I'm searching is tblContacts and the field is HomeAddressStreet
thanks kindly.

 

[John W. Vinson]

I need a simple update query to find the & sign and replace it with {and}
...the & sign gives trouble on down the road. Can someone please show me how
to do this in a query? I know it should be hard, but my brain seems froze!
thanks.
oh, my table I'm searching is tblContacts and the field is HomeAddressStreet
thanks kindly.

UPDATE tblContacts
SET HomeAddressStreet = Replace([HomeAddressStreet], "&", "{and}")
WHERE HomeAddressStreet LIKE "*[&]*";

should do it.


John W. Vinson [MVP]

 

[Southern at Heart]

Thanks, that worked great (I took out the {} signs though)
I wonder would it be easy to change this a little to do this replace on the
the whole table? All the fields in it (there are about 6, and though it'll
be rare that the & sign shows up in the others, if it does it'll make an
error that the user won't know how to fix (and I probably won't remember how
either!)
thanks,
S at H

 

[fredg]

I need a simple update query to find the & sign and replace it with {and}
...the & sign gives trouble on down the road. Can someone please show me how
to do this in a query? I know it should be hard, but my brain seems froze!
thanks.
oh, my table I'm searching is tblContacts and the field is HomeAddressStreet
thanks kindly.

Update tblContacts Set tblContacts.[HomeAddressStreet] =
Replace([HomeAddressStreet],"&","and")

What kind of trouble down the road?

 

[Southern at Heart]

Update tblContacts Set tblContacts.[HomeAddressStreet] =
Replace([HomeAddressStreet],"&","and")
....this looks like it will still only do the one field in my table. Isn't
there a way to do this replace on something like * (all the fields in the
table?)
thanks.


ps. the & sign is not allowed in a .gpx file for Garmin

 

[Robert Morley]

You would either have to do the fields one-by-one (though you can update all
of them in the same query), or you can simply open the table in Access's GUI
and do your search & replace from there. If you take the second approach,
it'll take longer to do the replace, but it's far easier than setting up a
six-column update.

To do the multi-column update, use the following:

Update tblContacts Set tblContacts.[HomeAddressStreet] =
Replace([HomeAddressStreet],"&","and"), tblContacts.NextField =
Replace([NextField],"&","and"), etc.


Rob

 

[Southern at Heart]

Okay, so I created this:
Update tblContacts Set tblContacts.[HomeAddressStreet] =
Replace([HomeAddressStreet],"&","and"), tblContacts.[FirstName] =
Replace([FirstName],"&","and"), tblContacts.[LastName] =
Replace([LastName],"&","and"), tblContacts.[HomeAddressCity] =
Replace([HomeAddressCity],"&","and"), tblContacts.[HomeAddressState] =
Replace([HomeAddressState],"&","and")

....so I don't need the Where ...like *&* statement in there then?

thanks,

 

[Robert Morley]

It's useful to speed things up, and particularly important if you're using
replication or some other kind of change-tracking, but not strictly
necessary, no.

If you find the query below is inordinately slow, then you could do it as
six separate queries and re-include the WHERE statement.

Alternatively, you could go for a half-way measure and include a where
statement for each of the six fields. This would still update all six
fields, even if only one of them needed it, but if only a small percentage
of your records contain an & in one of the fields, it could still provide a
significant gain.


Rob

Okay, so I created this:
Update tblContacts Set tblContacts.[HomeAddressStreet] =
Replace([HomeAddressStreet],"&","and"), tblContacts.[FirstName] =
Replace([FirstName],"&","and"), tblContacts.[LastName] =
Replace([LastName],"&","and"), tblContacts.[HomeAddressCity] =
Replace([HomeAddressCity],"&","and"), tblContacts.[HomeAddressState] =
Replace([HomeAddressState],"&","and")

....so I don't need the Where ...like *&* statement in there then?

thanks,

You would either have to do the fields one-by-one (though you can update all
of them in the same query), or you can simply open the table in Access's GUI
and do your search & replace from there. If you take the second approach,
it'll take longer to do the replace, but it's far easier than setting up a
six-column update.

To do the multi-column update, use the following:

Update tblContacts Set tblContacts.[HomeAddressStreet] =
Replace([HomeAddressStreet],"&","and"), tblContacts.NextField =
Replace([NextField],"&","and"), etc.


Rob