Find and replace
Chris Savedge
I have a table that has a couple of fields that I have to go through and do
a find and replace on. I have to do this because we get crap data. Anyway,
is there a way to do this with code? I always have to find a single quote
and replace it with a double. Can anyone give me an example of how to do
this with code?
Thanks,
Chris
Dirk Goldgar
Sure. You need two things: a function that will replace one specified
string with another in a given field or text string, and an update query
that applies that function, with suitable arguments, to the records in your
table.
I'm attaching a file, "fncReplaceText.bas" that defines a string-replacement
function. Make a backup copy of your database before proceeding. Now
paste this function into a standard module, so that it is available
throughout your database.
Now create an update query with SQL along these lines:
UPDATE tblMyTable SET MyField = fncReplaceText([MyField ],"'","""")
WHERE (InStr([MyField ],"'")>0);
Execute this query, and it will change all single quotes in MyField to
double quotes. If you save the query, you can execute it whenever you need
to, programmatically. You can create a variety of similar queries to fix up
whatever common data entry problems you have.
--
Dirk Goldgar
(remove NOSPAM from reply address)
"Chris Savedge" <chr...@legendvalve.com> wrote in message
news:OwFzroo...@cppssbbsa02.microsoft.com...
Dirk Goldgar
Whoops! I forgot to attach the file. I'm at home now, and it's not
available. Sorry. I'll post it in the morning.
--
Dirk Goldgar
(to reply via email, remove NOSPAM from address)
Dirk Goldgar wrote in message ...
Chris Savedge
Thanks, so much. I'm looking forward to getting your attachment.
Tony Toews
>I'm attaching a file, "fncReplaceText.bas" that defines a string-replacement
>function.
Please don't attach a file. Instead include the code inside a
message. Fortunately you didn't include the entire MDB in which case
you'd be getting a lot of blasts. <smile>
Tony
----
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
VolStar http://www.volstar.com Manage hundreds or
thousands of volunteers for special events.
Dirk Goldgar
Could you explain to me why including the text of the function in the
message should be preferable to attaching a text file? I'm hoping to with
avoid the confusion caused by line wrapping done by the mailreader.
--
Dirk Goldgar
(to reply via email, remove NOSPAM from address)
Tony Toews wrote in message <54h7ssoecdbflg06m...@4ax.com>...
Tony Toews
>Could you explain to me why including the text of the function in the
>message should be preferable to attaching a text file? I'm hoping to with
>avoid the confusion caused by line wrapping done by the mailreader.
Ah, gotcha. Actually your attached file was quite small so putting it
in the newsgroup would be just fine.
It's just that some folks put entire 1 Mb MDBs in a newsgroup. Many
folks who follow the newsgroups don't have the cheap Internet access
we have in Canada and the U.S. Most countries still charge by the
minute even for local calls.
One idiot student once emailed me an 3 MB MDB for me to review and do
his homework.
1) He never asked me.
2) Much of that 3 Mb was in a very colourful and totally inappropriate
background image on his menu which also meant you could'nt even see
the menu items.
3) More of that 3 Mb would've been cleaned up with compacting
4) He never even bothered to zip or otherwise compress it.
5) I was on a modem at the time which made things much, much worse.
Joan Wild
Tony Toews wrote in message ...
>One idiot student once emailed me an 3 MB MDB for me to review and do
>his homework.
>1) He never asked me.
>2) Much of that 3 Mb was in a very colourful and totally inappropriate
>background image on his menu which also meant you could'nt even see
>the menu items.
You opened it Tony?
Joan
Dirk Goldgar
"Tony Toews" <tto...@telusplanet.net> wrote in message
news:eiaass4o3hp6t59co...@4ax.com...
>
> It's just that some folks put entire 1 Mb MDBs in a newsgroup. Many
> folks who follow the newsgroups don't have the cheap Internet access
> we have in Canada and the U.S. Most countries still charge by the
> minute even for local calls.
Yes, I know; I was once in the same boat, paying for my internet connection
by the byte transferred. Even now, I have a relatively slow dial-up
connection, so I try to be scrupulous about the sizes of my posts.
Tony Toews
"Joan Wild" <jw...@nospamtyenet.com> wrote:
>You opened it Tony?
Well he had me quite curious by now.
Tony (a curious cat is a dead cat)