Punctuation blowup

[davisgwe]

In a table created in Access 2007 when punctuation was used in the field, it no longer allows pivot charts created in Access 2007 to show when referencing that field when using Access 2010. Remember getting a punctuation or metadata error message. Can we do a global replacement for punctuation or is there another program that we can integrate into Access 2010 rather than manually change thousands of entries in the 2007 Access documents?

[June7]

Recommend Rick Fisher's Find and Replace. Saved my sanity. Well worth the $50. http://www.rickworld.com/download.html

[rpeare]

if you know the field you want to search you can cycle through all the records in a table and replace a text character

for instance

Let's say I have a table called tblCommaExampleTable
The table has a field CommaExampleField

I have two records
Test, Test, Test, Test
Test Test, Test Test

If I remove commas these should both look identical.

To do this I run this:

Sub ReplaceCommas()
Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM tblCommaExampleTable")

Do While rst.EOF <> True
    rst.Edit
    rst.Fields("CommaExampleField") = Replace(rst.Fields("commaexamplefield"), ",", "")
    rst.Update
    rst.MoveNext
Loop

Set db = Nothing

End Sub

If you have multiple tables with multiple fields it's a little more complicated but this is the basic structure that will remove unwanted punctuation.