Text conversion or substitution

[Stuart Ramsbottom]

Hi,

I was wanting to know the easiest way to substitute a
field of data in ms access using sql for all the rows in a
column.

ie if l have a field that is say
"micro+soft+access"

I need to be able to change it to
"micro soft access"
using an SQL DML update statement do to the size of the
database.

Kind Regards

 

[Brendan Reynolds \(MVP\)]

Assuming a recent version of Access (Replace() was new in Access 2000, and
didn't work in queries until Access 2002) replace 'Students' and 'First
Name' in the example below with the name of your actual table and field ...

UPDATE Students SET Students.[First Name] = Replace([First Name],"+"," ")
WHERE ((InStr([First Name] & "","+")>"0"));

In Access 2000, while you can't use the Replace() function directly in
query, you can call a custom VBA function from the query and have the custom
function call the Replace() function.