I'm trying to run an update query on all fields in a table.
Like for example, replace all Chr(10) or Chr(13) with a coma.
It also needs to skip the ID field, as it is an autonumber.
How can I do this?
Here's my code, but it doesn't work....
Like for example, replace all Chr(10) or Chr(13) with a coma.
It also needs to skip the ID field, as it is an autonumber.
How can I do this?
Here's my code, but it doesn't work....
Code:
Public Function GetRidOf_dollar() As Boolean
'field all rows of all fields in a table
Dim fld As DAO.Field
Dim db As DAO.Database
Dim strSQL As String
Dim tbl As String
Set db = CurrentDb
tbl = "ProcessTable"
'loop through fields
For Each fld In db.TableDefs(tbl).Fields
'create an update SQL statement for each field
If fld.Name <> ID Then
strSQL = "Update [" & tbl & "] Set [" & fld.Name & "]= Replace([" & fld.Name & "],Chr(10),',')"
'run the query
CurrentDb.Execute strSQL, dbFailOnError 'roll back if error occurs
End If
Next fld
End Function