DoCmd Find and Replace

[Joe]

Does anyone know a VB DoCmd for Find and Replace in an entire table?

I need to search for N/A and replace it with Undefined on a table.

Thanks

 

[fredg]

Does anyone know a VB DoCmd for Find and Replace in an entire table?

I need to search for N/A and replace it with Undefined on a table.

Thanks

Back up your table first.

Run an Update query.
Update YourTable Set YourTable.FieldName =
Replace([FieldName],"N/A","Undefined");

 

[Joe]

Thanks fred... That was my initial thought as well. I'll create a macro with
a several update queries.

Thanks

Does anyone know a VB DoCmd for Find and Replace in an entire table?

I need to search for N/A and replace it with Undefined on a table.

Thanks

Back up your table first.

Run an Update query.
Update YourTable Set YourTable.FieldName =
Replace([FieldName],"N/A","Undefined");

 

[Rastro]

You don't need to create any query, only run them with VBA.

For example add a form and put in two textboxes (txtTable and txtField) and
a command button btnClearNA

On the click event of the button:

Private Sub btnClearNA_Click()
Dim strSQL as String

If IsNull(txtTable) or IsNull(txtTable) then Exit Sub

strSQL="UPDATE " & txtTable & " SET " & _
txtTable & "." & txtField & " = 'Undefined' " & _
"WHERE " & txtTable & "." & txtField & " = 'N/A' "

DoCmd.RunSQL (SQL)

End Sub

Thanks fred... That was my initial thought as well. I'll create a macro
with
a several update queries.

Thanks

Does anyone know a VB DoCmd for Find and Replace in an entire table?

I need to search for N/A and replace it with Undefined on a table.

Thanks

Back up your table first.

Run an Update query.
Update YourTable Set YourTable.FieldName =
Replace([FieldName],"N/A","Undefined");

 

[Rastro]

I made a mistake. This sentence:
DoCmd.RunSQL (SQL)

is:
DoCmd.RunSQL (strSQL)

And also forgot to tell you how it works:
You fill the textboxes with the names of the table and field which has the
"N/A" values that you want to replace and, before that, click the button.

Rastro

Thanks fred... That was my initial thought as well. I'll create a macro
with
a several update queries.

Thanks

Does anyone know a VB DoCmd for Find and Replace in an entire table?

I need to search for N/A and replace it with Undefined on a table.

Thanks

Back up your table first.

Run an Update query.
Update YourTable Set YourTable.FieldName =
Replace([FieldName],"N/A","Undefined");

 

[Joe]

Thanks Rastro... I will try to build the VB code...

I made a mistake. This sentence:
DoCmd.RunSQL (SQL)

is:
DoCmd.RunSQL (strSQL)

And also forgot to tell you how it works:
You fill the textboxes with the names of the table and field which has the
"N/A" values that you want to replace and, before that, click the button.

Rastro

Thanks fred... That was my initial thought as well. I'll create a macro
with
a several update queries.

Thanks

On Fri, 16 May 2008 13:06:06 -0700, Joe wrote:

Does anyone know a VB DoCmd for Find and Replace in an entire table?

I need to search for N/A and replace it with Undefined on a table.

Thanks

Back up your table first.

Run an Update query.
Update YourTable Set YourTable.FieldName =
Replace([FieldName],"N/A","Undefined");