Find and replace values across multiple fields

[atarbaer]

I would like to find and replace several values in my Access table.
For example, I would like to change all "%40" to "@", but rather than
specify a single field/column of my table to search through, I would
like the code to search through all of the fields in my table (there
are too many replacements and too many field names to specify).

How can I modify the code below so that it searches through each field
in the table?


Public Function RunCleanValues()

DoCmd.RunSQL "UPDATE [mytable] SET [fieldname] =
Replace([fieldname],'%40','@');"

End Function


Thank you in advance.

 

[Brendan Reynolds]

Public Sub TestSub()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM Table1")
Do Until rst.EOF
rst.Edit
For Each fld In rst.Fields
If fld.Type = dbText Or fld.Type = dbMemo Then
fld.Value = Replace(fld.Value, "%40", "@")
End If
Next fld
rst.Update
rst.MoveNext
Loop
rst.Close

End Sub

 

[atarbaer]

I would like to find and replace several values in my Access table.
For example, I would like to change all "%40" to "@", but rather than
specify a single field/column of my table to search through, I would
like the code to search through all of the fields in my table (there
are too many replacements and too many field names to specify).

How can I modify the code below so that it searches through each field
in the table?

Public Function RunCleanValues()

DoCmd.RunSQL "UPDATE [mytable] SET [fieldname] =
Replace([fieldname],'%40','@');"

End Function

Thank you in advance.

Perhaps someone can help me with the following code:

----------

Public Function RunCleanValues()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs("mytable")

For Each fld In tdf.Fields
fld.Name = Replace(fld.Name, "%40", "@")

Next fld


End Function

 

[Douglas J. Steele]

Your code should change the names of the fields in the table, not the
values.

Brendan's suggestion will change the values stored in the table, not the
names of the fields.

Which are you trying to do?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I would like to find and replace several values in my Access table.
For example, I would like to change all "%40" to "@", but rather than
specify a single field/column of my table to search through, I would
like the code to search through all of the fields in my table (there
are too many replacements and too many field names to specify).

How can I modify the code below so that it searches through each field
in the table?

Public Function RunCleanValues()

DoCmd.RunSQL "UPDATE [mytable] SET [fieldname] =
Replace([fieldname],'%40','@');"

End Function

Thank you in advance.

Perhaps someone can help me with the following code:

----------

Public Function RunCleanValues()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs("mytable")

For Each fld In tdf.Fields
fld.Name = Replace(fld.Name, "%40", "@")

Next fld


End Function

 

[atarbaer]

Your code should change the names of the fields in the table, not the
values.

Brendan's suggestion will change the values stored in the table, not the
names of the fields.

Which are you trying to do?

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)

I would like to find and replace several values in my Access table.
For example, I would like to change all "%40" to "@", but rather than
specify a single field/column of my table to search through, I would
like the code to search through all of the fields in my table (there
are too many replacements and too many field names to specify).
How can I modify the code below so that it searches through each field
in the table?
Public Function RunCleanValues()
DoCmd.RunSQL "UPDATE [mytable] SET [fieldname] =
Replace([fieldname],'%40','@');"
End Function
Thank you in advance.

Perhaps someone can help me with the following code:

Public Function RunCleanValues()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs("mytable")

For Each fld In tdf.Fields
fld.Name = Replace(fld.Name, "%40", "@")

Next fld

End Function- Hide quoted text -

- Show quoted text -

Thanks for catching that error -- I am trying to change the values of
the fields, not the field names.
If I change the code to the below, I get a run-time error 3219 on the
line:

fld.Value = Replace(fld.Value, "%40", "@")

Suggestions?

---

Public Function RunCleanValues()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field


Set db = CurrentDb
Set tdf = db.TableDefs("IAN data")


For Each fld In tdf.Fields
If fld.Type = dbText Or fld.Type = dbMemo Then
fld.Value = Replace(fld.Value, "%40", "@")
End If
Next fld


End Function

 

[atarbaer]

Your code should change the names of the fields in the table, not the
values.

Brendan's suggestion will change the values stored in the table, not the
names of the fields.

Which are you trying to do?

news:[email protected]...

On Mar 22, 9:57 am, (e-mail address removed) wrote:
I would like to find and replace several values in my Access table.
For example, I would like to change all "%40" to "@", but rather than
specify a single field/column of my table to search through, I would
like the code to search through all of the fields in my table (there
are too many replacements and too many field names to specify).
How can I modify the code below so that it searches through each field
in the table?
Public Function RunCleanValues()
DoCmd.RunSQL "UPDATE [mytable] SET [fieldname] =
Replace([fieldname],'%40','@');"
End Function
Thank you in advance.
Perhaps someone can help me with the following code:
----------
Public Function RunCleanValues()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set db = CurrentDb
Set tdf = db.TableDefs("mytable")
For Each fld In tdf.Fields
fld.Name = Replace(fld.Name, "%40", "@")
Next fld
End Function- Hide quoted text -

- Show quoted text -

Thanks for catching that error -- I am trying to change the values of
the fields, not the field names.
If I change the code to the below, I get a run-time error 3219 on the
line:

fld.Value = Replace(fld.Value, "%40", "@")

Suggestions?

---

Public Function RunCleanValues()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs("IAN data")

For Each fld In tdf.Fields
If fld.Type = dbText Or fld.Type = dbMemo Then
fld.Value = Replace(fld.Value, "%40", "@")
End If
Next fld

End Function- Hide quoted text -

- Show quoted text -

Brendan's code almost works -- I get an error "Invalid Use of Null".
If I hit Debug, the values of the fields are changed, but I can't
circumvent the error message. Thanks in advance for your help.

 

[John W. Vinson]

Brendan's code almost works -- I get an error "Invalid Use of Null".
If I hit Debug, the values of the fields are changed, but I can't
circumvent the error message. Thanks in advance for your help.

one line change:

rst.Edit
For Each fld In rst.Fields
If (fld.Type = dbText Or fld.Type = dbMemo) _
AND Not IsNull(fld.Value) Then
fld.Value = Replace(fld.Value, "%40", "@")
End If
Next fld

John W. Vinson [MVP]

 

[Douglas J. Steele]

<picky>It's more than one line, John. For one thing, he's trying to use the
TableDef object, rather than a recordset.</picky>

I think it would be better to use an Update query myself:

DoCmd.RunSQL "UPDATE [mytable] SET [fieldname] = " & _
"Replace([fieldname],'%40','@') WHERE [fieldname] IS NOT NULL"

To use a recordset, you'd need

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT * FROM [mytable]")
Do Until rsCurr.EOF = True
rsCurr.Edit
For Each fldCurr In rsCurr.Fields
If (fldCurr.Type = dbText Or fldCUrr.Type = dbMemo) _
AND Not IsNull(fldCurr.Value) Then
fldCurr.Value = Replace(fldCyrr.Value, "%40", "@")
End If
Next fld
rsCurr.MoveNext
Loop
rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

 

[John W. Vinson]

I think it would be better to use an Update query myself:

DoCmd.RunSQL "UPDATE [mytable] SET [fieldname] = " & _
"Replace([fieldname],'%40','@') WHERE [fieldname] IS NOT NULL"

well... yes, but the OP wanted to blindly update ALL fields.

Good point about the recordset...

John W. Vinson [MVP]

 

[Brendan Reynolds]

Should work now with John's or Doug's modification. (Note to self ...
remember to include Null values in test data next time). Did you get it
sorted?

--
Brendan Reynolds
Access MVP

Your code should change the names of the fields in the table, not the
values.

Brendan's suggestion will change the values stored in the table, not
the
names of the fields.

Which are you trying to do?

On Mar 22, 9:57 am, (e-mail address removed) wrote:
I would like to find and replace several values in my Access table.
For example, I would like to change all "%40" to "@", but rather
than
specify a single field/column of my table to search through, I would
like the code to search through all of the fields in my table (there
are too many replacements and too many field names to specify).

How can I modify the code below so that it searches through each
field
in the table?

Public Function RunCleanValues()

DoCmd.RunSQL "UPDATE [mytable] SET [fieldname] =
Replace([fieldname],'%40','@');"

End Function

Thank you in advance.

Perhaps someone can help me with the following code:

Public Function RunCleanValues()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs("mytable")

For Each fld In tdf.Fields
fld.Name = Replace(fld.Name, "%40", "@")

Next fld

End Function- Hide quoted text -

- Show quoted text -

Thanks for catching that error -- I am trying to change the values of
the fields, not the field names.
If I change the code to the below, I get a run-time error 3219 on the
line:

fld.Value = Replace(fld.Value, "%40", "@")

Suggestions?

---

Public Function RunCleanValues()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs("IAN data")

For Each fld In tdf.Fields
If fld.Type = dbText Or fld.Type = dbMemo Then
fld.Value = Replace(fld.Value, "%40", "@")
End If
Next fld

End Function- Hide quoted text -

- Show quoted text -

Brendan's code almost works -- I get an error "Invalid Use of Null".
If I hit Debug, the values of the fields are changed, but I can't
circumvent the error message. Thanks in advance for your help.