Question about using Update query replace function together-Fails in Access 2002

  • Thread starter Thread starter Mike Strout
  • Start date Start date
M

Mike Strout

I have a fairly large Sql2k DB in which I am trying to update some addresses
so the Post Office will deliver our statements. Here is the query I am
trying to run...

UPDATE dbo_PATIENT SET dbo_PATIENT.ADDRESS =
Replace([dbo_PATIENT.ADDRESS],[Enter old string],[Enter new string])
WHERE (((dbo_PATIENT.ADDRESS) Like "* " & [Enter old string] & " *"));

What I am trying to accomplish is to replace all instances of a given
substring in the address field based on the two parameters entered by the
user. For example, the field may contain...

1234 CR 47

I want to query the table and replace all instances of "CR" with "Country
Road". You may notice that I left spaces between the wildcards and the
parameters. This is so "987 Creekside Trail" doesn't get changed to "987
County Roadeekside Trail".

Hopefully this all makes sense. Now the problem. When I click on the
Datasheet view in access, I will get a correct match on 1234 CR 47 and it
will be displayed. However, when I actually try to execute the query, I get
0 matching rows, even though I enter the parameters exactly the same.

What is going on?

Mike
<><
 
Mike Strout said:
I have a fairly large Sql2k DB in which I am trying to update some
addresses so the Post Office will deliver our statements. Here is the
query I am trying to run...

UPDATE dbo_PATIENT SET dbo_PATIENT.ADDRESS =
Replace([dbo_PATIENT.ADDRESS],[Enter old string],[Enter new string])
WHERE (((dbo_PATIENT.ADDRESS) Like "* " & [Enter old string] & " *"));

What I am trying to accomplish is to replace all instances of a given
substring in the address field based on the two parameters entered by
the user. For example, the field may contain...

1234 CR 47

I want to query the table and replace all instances of "CR" with
"Country Road". You may notice that I left spaces between the
wildcards and the parameters. This is so "987 Creekside Trail"
doesn't get changed to "987 County Roadeekside Trail".

Hopefully this all makes sense. Now the problem. When I click on the
Datasheet view in access, I will get a correct match on 1234 CR 47
and it will be displayed. However, when I actually try to execute the
query, I get 0 matching rows, even though I enter the parameters
exactly the same.

What is going on?

Mike
<><
Click to expand...

How are you executing the query? Directly from the query design view,
or using code (and if code, what code)? Is this in an MDB file with
linked tables (sounds like it), or in ADP?
 
Back
Top