Find And Replace text in a memo field

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

You could try this untested SQL

UPDATE (RequestNote INNER JOIN Request
ON RequestNOte.RequestID = Request.RequestID)
INNER JOIN LocationBridge
ON Request.Location =LocationBridge.Location
SET RequestNote.Note =
Replace([RequestNote].[Note],"555-555-5555",[LocationBridge].[DDPhone])
WHERE Request.ProjectTaskID In (98,99,100)
AND RequestNote.Note Like "*555-555-5555*"



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

TraciAnn via AccessMonster.com wrote:
> Out of hundreds of thousands of records, there are about 3,000 that have a
> phone number in a Memo field that is incorrect and needs changed.
>
> The incorrect number is the same on all records, but the new number it needs
> to change to is unique to the record.
>
> There are records where the above mentioned "incorrect" number is actually
> correct. So I only want to change it on certain records - records matching
> ProjectTaskID in (98,99,100).
>
> Details
> SQL backend
> RequestNote table
> RequestNoteID (PK)
> RequestID (FK)
> Note (varchar(7900))
>
> Request table
> RequestID (PK)
> ProjectTaskID (FK)
> Location (FK)
>
> LocationBridge
> Location (PK)
> DDPhone (Text)
>
> I need to select RequestNote.Note Where Request.ProjectTaskID In (98,99,100)
> AND RequestNote.Note (contains) "555-555-5555"
>
> Then replace "555-555-5555" with LocationBridge.DDPhone where LocationBridge.
> Location = Request.Location
>
> Thanks for your help!
>

TraciAnn via AccessMonster.com wrote:
> I need to select RequestNote.Note Where Request.ProjectTaskID In (98,99,100)
> AND RequestNote.Note (contains) "555-555-5555"
>
> Then replace "555-555-5555" with LocationBridge.DDPhone where LocationBridge.
> Location = Request.Location

Hi TraciAnn

I built an UPDATE statement which was nearly identical to the one John
showed you. I tested mine against native Jet tables, and it worked as
intended. I don't see why it wouldn't work with linked SQL Server tables.

If you have trouble, try a SELECT statement to make sure you're trying
to operate on the correct SQL Server records.

SELECT
n.RequestNoteID
, n.RequestID
, n.[Note]
, r.ProjectTaskID
, r.Location
, l.DDPhone
FROM
(RequestNote AS n
INNER JOIN Request AS r
ON n.RequestID = r.RequestID)
INNER JOIN LocationBridge AS l
ON r.Location = l.Location
WHERE
(((n.[Note]) Like "*555-555-5555*")
AND ((r.ProjectTaskID) In (98,99,100)));

One possible complication is that, if you execute the UPDATE statement
through an ADO connection, you will need to change the wild card
character from "*" to "%".

Also notice I placed brackets around your "Note" field name. Note is a
reserved word for Jet. Not sure it would make any difference when Note
is a SQL Server field ... I bracketed it just in case.
http://allenbrowne.com/AppIssueBadWord.html#N

Keven,

>Why do you need the phone number in the Note field at all? You already
>have it stored properly in the LocationBridge.DDPhone field, you
>shouldn't store it again in the Note field.

I appreciate the observation, but suffice it to say that the note field is
serving it's purpose and the tables are adequately normalized. This specific
Note field is used to populate a report with Memo style information which, in
this case, was entered with the wrong number.

The LocationBridge table was added to an AcBE (not the SQL) in order to
populate the number to the Note field.

Thanks for checking!

--
---
TraciAnn

Message posted via http://www.accessmonster.com

Hi Hans!

>I built an UPDATE statement which was nearly identical to the one John
>showed you. I tested mine against native Jet tables, and it worked as
>intended. I don't see why it wouldn't work with linked SQL Server tables.

Awesome! So, I'm trying your SELECT first before I tie in John's UPDATE
statement.

>If you have trouble, try a SELECT statement to make sure you're trying
>to operate on the correct SQL Server records.
>
>SELECT
> n.RequestNoteID
> , n.RequestID
> , n.[Note]
> , r.ProjectTaskID
> , r.Location
> , l.DDPhone
>FROM
> (RequestNote AS n
> INNER JOIN Request AS r
> ON n.RequestID = r.RequestID)
> INNER JOIN LocationBridge AS l
> ON r.Location = l.Location
>WHERE
> (((n.[Note]) Like "*555-555-5555*")
> AND ((r.ProjectTaskID) In (98,99,100)));
>
>One possible complication is that, if you execute the UPDATE statement
>through an ADO connection, you will need to change the wild card
>character from "*" to "%".

Sorry I left that part out....I have an ODBC connection

>Also notice I placed brackets around your "Note" field name. Note is a
>reserved word for Jet. Not sure it would make any difference when Note
>is a SQL Server field ... I bracketed it just in case.

Should I expect this SELECT to return the current contents of RequestNote.
Note?
The values are displayed for all other columns but Note is Null.

Thanks for your help!

--
---
TraciAnn

Message posted via http://www.accessmonster.com

TraciAnn via AccessMonster.com wrote:
>> One possible complication is that, if you execute the UPDATE statement
>> through an ADO connection, you will need to change the wild card
>> character from "*" to "%".
>
> Sorry I left that part out....I have an ODBC connection

In case you were to try:

CurrentProject.Connection.Execute "yourUpdateSqlStatement"

CurrentProject.Connection is an ADO Connection object and you will need
to use "Like '%555-555-5555%'" in your UPDATE criteria. When you're
using ADO, it doesn't matter whether the table is an ODBC link or a
native Jet table --- you still need the ANSI wild cards ADO requires.

> Should I expect this SELECT to return the current contents of RequestNote.
> Note?

It did on mine. I hoped it would with yours, too.

> The values are displayed for all other columns but Note is Null.

How are you executing the UPDATE statement? Did you try the alternate
wild card character?

TraciAnn via AccessMonster.com wrote:
> Should I expect this SELECT to return the current contents of
RequestNote.
> Note?
> The values are displayed for all other columns but Note is Null.

Could be a display issue if your Note field starts with a blank line
before the actual text.

Maybe test by adding a field expression to your SELECT statement:

Len(n.[Note] & "") AS LengthOfNote