A97 replace question?

[MLH]

I have memo field containing imported data.
In it are numerous Chr(13) & Chr(13) & Chr$(10)
occurrences. How do I rid my table of all occurrences
of this 3-character string ?

Generally there is only a single occurrence in
the memo field of any record.

[MLH]

Re: A97 replace question?

One more thing, I'm able to extract the problem records
using this query,,,

SELECT tblNewsPaperWeb sitesWORKINGCOP Y.IDfield,
tblNewsPaperWeb sitesWORKINGCOP Y.SixtyCharsAft erURL
FROM tblNewsPaperWeb sitesWORKINGCOP Y
WHERE (((tblNewsPaper WebsitesWORKING COPY.SixtyChars AfterURL) Like "*"
& Chr$(13) & Chr$(13) & Chr$(10) & "*"));

But I don't know how to chop out those undesireables
that are embedded in mid-string with an update query
strategy. To do it manually will be a lot of work.

[Lord Kelvan]

Re: A97 replace question?

you could try

update tblNewsPaperWeb sitesWORKINGCOP Y
set SixtyCharsAfter URL = Replace(SomeFie ld,Chr(13) & Chr(13) &
CHr(10), "")
WHERE (((tblNewsPaper WebsitesWORKING COPY.SixtyChars AfterURL) Like "*"
& Chr$(13) & Chr$(13) & Chr$(10) & "*"));

but this wont work if you dont have access 2000 or later

Regards
Kelvan
---------------------
Nothing's Impossible; merely Improbable.

[Lord Kelvan]

Re: A97 replace question?

you could try

update tblNewsPaperWeb sitesWORKINGCOP Y
set SixtyCharsAfter URL = Replace(SixtyCh arsAfterURL ,Chr(13) & Chr(13)
&
CHr(10), "")
WHERE (((tblNewsPaper WebsitesWORKING COPY.SixtyChars AfterURL) Like "*"
& Chr$(13) & Chr$(13) & Chr$(10) & "*"));


but this wont work if you dont have access 2000 or later


Regards
Kelvan
---------------------
Nothing's Impossible; merely Improbable.

[Larry Linson]

Re: A97 replace question?

You can, of course, use user-defined functions (aka UDFs) in Queries, just
as you can use the built-in Replace function in Access 2000 and later
versions. There is an implementation of replacing a string within a string
at http://www.mvps.org/access/strings/str0004.htm. The title indicates that
it is for replacing a character, but, in fact, it replaces a string of
whatever length you desire. If that doesn't work for you, using Google
Groups to search the archives should turn up many implementations of Replace
functions that people used prior to the Replace built-in function being
available in Access 2000.

Just for the record, http://www.mvps.org/access does not have everything
about Access, but it is a more-than-reasonable place to start looking,
because it has so much that is so useful.

Larry Linson
Microsoft Office Access MVP


"MLH" <CRCI@NorthStat e.netwrote in message
news:qk5pb49afe ul66ucl1fhm8bkj n2cvnivf6@4ax.c om...

>I have memo field containing imported data.
In it are numerous Chr(13) & Chr(13) & Chr$(10)
occurrences. How do I rid my table of all occurrences
of this 3-character string ?
>
Generally there is only a single occurrence in
the memo field of any record.

[MLH]

Re: A97 replace question?

Thanks, guys. What I ended up doing was not elegant.
But it was effective. I found the InStr point at which the
undesired string began. Knowing it was 3-chars in length,
I was able to concat Left$ and Right$ - extracting the parts
I wanted and leaving the undesirable out-a-the picture.

Once I got a column of values, I just copied 'em 'n pasted
'em over the crappie values. That did the trick. Thx. I'll look
into your suggestions for future reference.