Replacing in text access

[CarlM1975]

Hi

I need to replace carriage returns in a field in access. Now I know that replace([fieldname],chr(13)," ") will do what I need but I want to be able to apply this to all records in this particular field in one hit, and not create another field to ahcieve it.

Does anyone know the syntax for the module to achieve this.

Thanks

Carl.

 

[Rabbie]

Try looking for Update queries in Access help.

 

[DCrake]

Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("YourTable")
Do Until Rs.EOF
   Rs.Edit
   Rs("FieldName") = Replace(Rs("FieldName"),Chr(10),"")
   Rs.Update
   Rs.MoveNext
Loop
Rs.Close
Set Rs = Nothing

CodeMaster:

 

[Rabbie]

Using an update query will run faster than doing it in VBA

 

[CarlM1975]

The code produces the following error message:

Cannot open any more databases and stops on line 2 of the code

any ideas????