How to use update query for partial updating of a field

[Veli Izzet]

Hi all,

I want to partially update a text field; i.e. changing only a few words
of a sentence in that field.

Up to now, I exported the table to excell, do search&replace there and
imported back. This is clumsy and I am afraid there may be some
corruption in the table, so I want to be able to do it in access. I am
using the 2003 version.

How do I do this?

Regards

 

[Brendan Reynolds]

You can use the Replace() function in an update query. For example, the
following query will replace all instances of the abbreviation "Ave." in the
Address field in the Employees table with the word "Avenue" ...

UPDATE Employees SET Employees.Address = Replace([Address],"Ave.","Avenue");

In query design view, the expression in the "Update to" row looks like this
....

Replace([Address],"Ave.","Avenue")

 

[Veli Izzet]

Thanks..

 

[Guest]

Unless your computer, like mine, for some obscure
reason related to version, installation history, and security
patches, refuses to let you use Replace() in a query.

In which case you will have to write a VBA function
to call replace:

Function MyReplace(sSource,sFind,Sreplace) as string
MyReplace = replace(sSource,sFind,Sreplace)
end function

(david)

You can use the Replace() function in an update query. For example, the
following query will replace all instances of the abbreviation "Ave." in the
Address field in the Employees table with the word "Avenue" ...

UPDATE Employees SET Employees.Address = Replace([Address],"Ave.","Avenue");

In query design view, the expression in the "Update to" row looks like this
...

Replace([Address],"Ave.","Avenue")

--
Brendan Reynolds

Hi all,

I want to partially update a text field; i.e. changing only a few words of
a sentence in that field.

Up to now, I exported the table to excell, do search&replace there and
imported back. This is clumsy and I am afraid there may be some corruption
in the table, so I want to be able to do it in access. I am using the 2003
version.

How do I do this?

Regards

 

[Brendan Reynolds]

I know that used to happen with Access 2000, David. But the original poster
specified Access 2003, and I've never heard of a problem using the Replace()
function in a query in Access 2003. Are you seeing the problem in Access
2003?

--
Brendan Reynolds

Unless your computer, like mine, for some obscure
reason related to version, installation history, and security
patches, refuses to let you use Replace() in a query.

In which case you will have to write a VBA function
to call replace:

Function MyReplace(sSource,sFind,Sreplace) as string
MyReplace = replace(sSource,sFind,Sreplace)
end function

(david)

You can use the Replace() function in an update query. For example, the
following query will replace all instances of the abbreviation "Ave." in the
Address field in the Employees table with the word "Avenue" ...

UPDATE Employees SET Employees.Address = Replace([Address],"Ave.","Avenue");

In query design view, the expression in the "Update to" row looks like this
...

Replace([Address],"Ave.","Avenue")

--
Brendan Reynolds

Hi all,

I want to partially update a text field; i.e. changing only a few words of
a sentence in that field.

Up to now, I exported the table to excell, do search&replace there and
imported back. This is clumsy and I am afraid there may be some corruption
in the table, so I want to be able to do it in access. I am using the 2003
version.

How do I do this?

Regards

 

[Veli Izzet]

Well, it seems I am one of the chosen ones.... I succeeed using replace..

Unless your computer, like mine, for some obscure
reason related to version, installation history, and security
patches, refuses to let you use Replace() in a query.

In which case you will have to write a VBA function
to call replace:

Function MyReplace(sSource,sFind,Sreplace) as string
MyReplace = replace(sSource,sFind,Sreplace)
end function

(david)

You can use the Replace() function in an update query. For example, the
following query will replace all instances of the abbreviation "Ave." in
the

Address field in the Employees table with the word "Avenue" ...

UPDATE Employees SET Employees.Address =
Replace([Address],"Ave.","Avenue");

In query design view, the expression in the "Update to" row looks like
this

...

Replace([Address],"Ave.","Avenue")

--
Brendan Reynolds

Hi all,

I want to partially update a text field; i.e. changing only a few words

of

a sentence in that field.

Up to now, I exported the table to excell, do search&replace there and
imported back. This is clumsy and I am afraid there may be some

corruption

in the table, so I want to be able to do it in access. I am using the

2003

version.

How do I do this?

Regards

 

[Ken Snell [MVP]]

And that problem was fixed by Jet 4.0 SP6, I believe, for ACCESS 2000.

--

Ken Snell
<MS ACCESS MVP>

I know that used to happen with Access 2000, David. But the original poster
specified Access 2003, and I've never heard of a problem using the
Replace() function in a query in Access 2003. Are you seeing the problem in
Access 2003?

--
Brendan Reynolds

Unless your computer, like mine, for some obscure
reason related to version, installation history, and security
patches, refuses to let you use Replace() in a query.

In which case you will have to write a VBA function
to call replace:

Function MyReplace(sSource,sFind,Sreplace) as string
MyReplace = replace(sSource,sFind,Sreplace)
end function

(david)

You can use the Replace() function in an update query. For example, the
following query will replace all instances of the abbreviation "Ave." in the
Address field in the Employees table with the word "Avenue" ...

UPDATE Employees SET Employees.Address = Replace([Address],"Ave.","Avenue");

In query design view, the expression in the "Update to" row looks like this
...

Replace([Address],"Ave.","Avenue")

--
Brendan Reynolds

Hi all,

I want to partially update a text field; i.e. changing only a few
words of
a sentence in that field.

Up to now, I exported the table to excell, do search&replace there and
imported back. This is clumsy and I am afraid there may be some corruption
in the table, so I want to be able to do it in access. I am using the 2003
version.

How do I do this?

Regards

 

[Guest]

It was certainly fixed on my computer - and stopped working
again sometime after applying SP8. I'm not presently at that
computer. I'll try again with 2K2 and 2K3 when I get a chance.

(david)

And that problem was fixed by Jet 4.0 SP6, I believe, for ACCESS 2000.

--

Ken Snell
<MS ACCESS MVP>

I know that used to happen with Access 2000, David. But the original poster
specified Access 2003, and I've never heard of a problem using the
Replace() function in a query in Access 2003. Are you seeing the problem in
Access 2003?

--
Brendan Reynolds

Unless your computer, like mine, for some obscure
reason related to version, installation history, and security
patches, refuses to let you use Replace() in a query.

In which case you will have to write a VBA function
to call replace:

Function MyReplace(sSource,sFind,Sreplace) as string
MyReplace = replace(sSource,sFind,Sreplace)
end function

(david)

You can use the Replace() function in an update query. For example, the
following query will replace all instances of the abbreviation "Ave." in
the
Address field in the Employees table with the word "Avenue" ...

UPDATE Employees SET Employees.Address =
Replace([Address],"Ave.","Avenue");

In query design view, the expression in the "Update to" row looks like
this
...

Replace([Address],"Ave.","Avenue")

--
Brendan Reynolds

Hi all,

I want to partially update a text field; i.e. changing only a few
words
of
a sentence in that field.

Up to now, I exported the table to excell, do search&replace there and
imported back. This is clumsy and I am afraid there may be some
corruption
in the table, so I want to be able to do it in access. I am using the
2003
version.

How do I do this?

Regards

 

[david epsom dot com dot au]

Can't tell you more, because replace is working again
on the test PC's. Windows and Office have been
re-installed.

(david)

It was certainly fixed on my computer - and stopped working
again sometime after applying SP8. I'm not presently at that
computer. I'll try again with 2K2 and 2K3 when I get a chance.

(david)

And that problem was fixed by Jet 4.0 SP6, I believe, for ACCESS 2000.

--

Ken Snell
<MS ACCESS MVP>

I know that used to happen with Access 2000, David. But the original poster
specified Access 2003, and I've never heard of a problem using the
Replace() function in a query in Access 2003. Are you seeing the problem in
Access 2003?

--
Brendan Reynolds

Unless your computer, like mine, for some obscure
reason related to version, installation history, and security
patches, refuses to let you use Replace() in a query.

In which case you will have to write a VBA function
to call replace:

Function MyReplace(sSource,sFind,Sreplace) as string
MyReplace = replace(sSource,sFind,Sreplace)
end function

(david)

You can use the Replace() function in an update query. For example, the
following query will replace all instances of the abbreviation "Ave." in
the
Address field in the Employees table with the word "Avenue" ...

UPDATE Employees SET Employees.Address =
Replace([Address],"Ave.","Avenue");

In query design view, the expression in the "Update to" row looks
like
this
...

Replace([Address],"Ave.","Avenue")

--
Brendan Reynolds

Hi all,

I want to partially update a text field; i.e. changing only a few
words
of
a sentence in that field.

Up to now, I exported the table to excell, do search&replace there and
imported back. This is clumsy and I am afraid there may be some
corruption
in the table, so I want to be able to do it in access. I am using the
2003
version.

How do I do this?

Regards