By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,959 Members | 1,136 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,959 IT Pros & Developers. It's quick & easy.

A97 replace question?

P: n/a
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.
Sep 2 '08 #1
Share this Question
Share on Google+
5 Replies


P: n/a
MLH
One more thing, I'm able to extract the problem records
using this query,,,

SELECT tblNewsPaperWebsitesWORKINGCOPY.IDfield,
tblNewsPaperWebsitesWORKINGCOPY.SixtyCharsAfterURL
FROM tblNewsPaperWebsitesWORKINGCOPY
WHERE (((tblNewsPaperWebsitesWORKINGCOPY.SixtyCharsAfter URL) 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.


Sep 2 '08 #2

P: n/a
you could try

update tblNewsPaperWebsitesWORKINGCOPY
set SixtyCharsAfterURL = Replace(SomeField,Chr(13) & Chr(13) &
CHr(10), "")
WHERE (((tblNewsPaperWebsitesWORKINGCOPY.SixtyCharsAfter URL) 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.
Sep 2 '08 #3

P: n/a
you could try

update tblNewsPaperWebsitesWORKINGCOPY
set SixtyCharsAfterURL = Replace(SixtyCharsAfterURL ,Chr(13) & Chr(13)
&
CHr(10), "")
WHERE (((tblNewsPaperWebsitesWORKINGCOPY.SixtyCharsAfter URL) 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.

Sep 2 '08 #4

P: n/a
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" <CR**@NorthState.netwrote in message
news:qk********************************@4ax.com...
>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.

Sep 2 '08 #5

P: n/a
MLH
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.

Sep 3 '08 #6

This discussion thread is closed

Replies have been disabled for this discussion.