Find & Replace in a table

[giannis]

I have a table with a memo field.
When i search the memo for a word
to replace with an other word, access
replace all (entire) the memo with this
word !!!
What is wrong ?
(when i use find for a word, access
select all the text in memo, not only
this word)

 

[John Vinson]

I have a table with a memo field.
When i search the memo for a word
to replace with an other word, access
replace all (entire) the memo with this
word !!!
What is wrong ?

Nothing. It's doing what you ask. You're finding a *RECORD* using a
search criterion, and you're updating a *FIELD* - it's updating that
field (the entire field) to the value you specify.

(when i use find for a word, access
select all the text in memo, not only
this word)

That's just the way queries work.

What you can do, however, is use the Replace() function to update the
text in the Memo field to an edited version of the text in that field.
If you have AccessXP update to:

Replace([memofield], "oldword", "newword")

If you have Access 2000, you can't use Replace directly in a query; as
a getaround put this in a Module:

Public Function QReplace(strSource As String, strOld As String, strNew
As String) As String
QReplace = Replace(strSource, strOld, strNew)
End Function

and update using QReplace instead.

 

[giannis]

Why in Access 2000 i dont use Replace() in a query ?

I have a table with a memo field.
When i search the memo for a word
to replace with an other word, access
replace all (entire) the memo with this
word !!!
What is wrong ?

Nothing. It's doing what you ask. You're finding a *RECORD* using a
search criterion, and you're updating a *FIELD* - it's updating that
field (the entire field) to the value you specify.

(when i use find for a word, access
select all the text in memo, not only
this word)

That's just the way queries work.

What you can do, however, is use the Replace() function to update the
text in the Memo field to an edited version of the text in that field.
If you have AccessXP update to:

Replace([memofield], "oldword", "newword")

If you have Access 2000, you can't use Replace directly in a query; as
a getaround put this in a Module:

Public Function QReplace(strSource As String, strOld As String, strNew
As String) As String
QReplace = Replace(strSource, strOld, strNew)
End Function

and update using QReplace instead.

 

[John Vinson]

Why in Access 2000 i dont use Replace() in a query ?

Because it doesn't work! For some unaccountable reason, the
programmers set up the Replace function in a way that it cannot be
used in a Query. That's why you need the QReplace getaround. This
error in programming was fixed in AccessXP.