Strip Characters

[Mike]

On a form I have created a button that I want to strip the " from all records
in a table. the following code will strip the " from the listed field in the
table:

Set rst = db.OpenRecordset("Mars_ext_pln_002_1000_rows")
Do While Not rst.EOF
strTemp = ""
strWrite = ""
strTemp = rst.Fields("ACT_ID")
x = 1
For x = 1 To Len(strTemp)
If Mid(strTemp, x, 1) <> Chr(34) Then
strWrite = strWrite + Mid(strTemp, x, 1)
End If
Next x
rst.Edit
rst.Fields("ACT_ID") = strWrite
rst.Update
rst.MoveNext
Loop
rst.Close

Does anyone know of away to do the same for the entire table without
repeating the code for each field in the table?

 

[fredg]

On a form I have created a button that I want to strip the " from all records
in a table. the following code will strip the " from the listed field in the
table:

Set rst = db.OpenRecordset("Mars_ext_pln_002_1000_rows")
Do While Not rst.EOF
strTemp = ""
strWrite = ""
strTemp = rst.Fields("ACT_ID")
x = 1
For x = 1 To Len(strTemp)
If Mid(strTemp, x, 1) <> Chr(34) Then
strWrite = strWrite + Mid(strTemp, x, 1)
End If
Next x
rst.Edit
rst.Fields("ACT_ID") = strWrite
rst.Update
rst.MoveNext
Loop
rst.Close

Does anyone know of away to do the same for the entire table without
repeating the code for each field in the table?

How many fields?
3? 10?, 20?
No need to cycle through all the fields. If your table is normalized,
it would be quicker to use SQL and name the fields.
Run an update query.

Update YourTable Set YourTable.[Field1] =
Replace([Field1],"""",""),YourTable.[Field2] =
Replace([Field2],"""",""),YourTable.[Field3] =
Replace([Field3],"""","")
etc.
WHERE InStr([Field1],"""")>0 OR InStr([Field2],"""")>0 OR
InStr([Field3],"""")>0
etc.;

Change the Field and table names as needed.

If no record in the table has a Null entry, you don't need to use any
criteria.

 

[Al Campagna]

Mike
Try Help on...
"Replace" or "Replacing values in Fields" (A97)
(I've never had any luck with A2003 Help)

1 In Form or Datasheet view, select the field (column) you want to search,
unless you want to search all fields. (Searching a single field is faster
than searching the entire table.)
2 On the Edit menu, click Replace.
3 In the Find What box, type the value you want to find; in the Replace With
box, type the value you want to replace it with.
4 Set any other options you want to use in the Replace dialog box.
5 To replace all occurrences of the specified value at once, click Replace
All.

Since your removing the ", the "Replace With" is left blank. I tested, and
it worked.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."