473,325 Members | 2,608 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

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.
Sep 2 '08 #1
5 1526
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

19
by: Westcoast Sheri | last post by:
To keep track of how many fruits my visitors buy, I use a mySQL database (2 columns: "fruit" and "quantity")....so can we make these following mySQL queries work somehow? (visitor buys 5...
5
by: Roose | last post by:
How can I do a "".replace operation which tells me if anything was actually replaced? Right now I am doing something like: if searchTerm in text: text = text.replace( searchTerm, 'other' ) ...
19
by: rbt | last post by:
Here's the scenario: You have many hundred gigabytes of data... possible even a terabyte or two. Within this data, you have private, sensitive information (US social security numbers) about your...
4
by: higabe | last post by:
Three questions 1) I have a string function that works perfectly but according to W3C.org web site is syntactically flawed because it contains the characters </ in sequence. So how am I...
3
by: Andy Sutorius | last post by:
Hi, I read the thread (2/16/05) regarding a replace function in C# however it didn't answer my question. I have a string which is building an insert sql statement and I would like to replace...
9
by: Peter Row | last post by:
Hi, I know this has been asked before, but reading the threads it is still not entirely clear. Deciding which .Replace( ) to use when. Typically if I create a string in a loop I always use a...
5
by: djc | last post by:
I need to prepare a large text database field to display in an asp.net repeater control. Currently I am replacing all chr(13)'s with a "<br/>" and it works fine. However, now I also want to be able...
8
by: Joe Cool | last post by:
I need to map several columns of data from one database to another where the data contains multiple spaces (once occurance of a variable number or spaces) that I need to replace with a single...
6
by: alainfri | last post by:
I am not sure if this group is the right place for this question but what I need is as follows. There is a piece of html. Throughout the html there are a lot of <brtags. The task is to replace all...
15
by: =?Utf-8?B?TWlrZSAiWU9fQkVFIiBC?= | last post by:
I have a text file that contains about 8 to 10 text sequences that I need to replace. I want to search and replace all 8 to 10 text sequence anytime I run this script Here is what I have so...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.