I searched and searched for something that would remove non-standard characters from my data and here is what I came up with. I wanted to post it so someone else looking for similar functionality could find it.
Background. We are migrating from Goldmine to Microsoft CRM Online. When moving notes, activities, and email there are lots of opportunities for people to add odd characters to the larger text fields. CRM uploads will fail if there are any unrecognized characters in the data at all. After several bubble type trouble shooting ventures by halving a table and uploading them both until I was down to about 10 records I could import into word and look for the special character I knew I had to find something new.
After searching and searching I found a piece of code that would allow me to remove unwanted characters but I couldn't find a piece of code that would just go and remove them all.
After finally looking at a table of ASCII values I realized the solution was simple I just needed to open my eyes. By incorporating for next loops to loop through the ASCII values I am able to check for every character possible. I then used a case statement to exclude the ASCII values that I didn't want it to do anything with and ta-da. All my troubled characters are gone. Thanks to those who helped get me started with the string replacement code.
I use this code to cycle through each and every field, in each record of a table to make sure I get them all.
It does take some time so I have a progress form that keeps me occupied while I wait. You can always take that out. While I am sure this is not the cleanest or simplest way to do it. It works great for me.
Have fun!:banghead:
Background. We are migrating from Goldmine to Microsoft CRM Online. When moving notes, activities, and email there are lots of opportunities for people to add odd characters to the larger text fields. CRM uploads will fail if there are any unrecognized characters in the data at all. After several bubble type trouble shooting ventures by halving a table and uploading them both until I was down to about 10 records I could import into word and look for the special character I knew I had to find something new.
After searching and searching I found a piece of code that would allow me to remove unwanted characters but I couldn't find a piece of code that would just go and remove them all.
After finally looking at a table of ASCII values I realized the solution was simple I just needed to open my eyes. By incorporating for next loops to loop through the ASCII values I am able to check for every character possible. I then used a case statement to exclude the ASCII values that I didn't want it to do anything with and ta-da. All my troubled characters are gone. Thanks to those who helped get me started with the string replacement code.
Code:
Public Function RemoveBadChar(FromString As Variant) As Variant
Dim i As Integer
If IsNull(FromString) Then 'handle Null values
RemoveBadChar = Null
Exit Function
End If
Dim strTemp As String
strTemp = FromString
For i = 1 To 31
Select Case i
Case 9, 10, 13
' do nothing
Case Else
strTemp = Replace(strTemp, Chr(i), " ")
End Select
Next
For i = 127 To 255
strTemp = Replace(strTemp, Chr(i), " ")
Next
While InStr(strTemp, " ") > 0
strTemp = Replace(strTemp, " ", " ")
Wend
strTemp = Trim(strTemp)
RemoveBadChar = strTemp
End Function
I use this code to cycle through each and every field, in each record of a table to make sure I get them all.
Code:
Function searchforbadstrings(tblName As String) As String
Dim rs1 As New ADODB.Recordset
Dim strSQLrs1 As String
Dim i As Integer
Dim strNewString As Variant
Dim intprog As Long
DoCmd.OpenForm "frm_progress"
intprog = 0
strSQLrs1 = "SELECT " & tblName & ".* FROM " & tblName & ";"
rs1.Open strSQLrs1, CurrentProject.Connection, adOpenStatic, adLockOptimistic
Do While Not rs1.EOF
If rs1.AbsolutePosition = 8700 Then
MsgBox "It will clear the MaxLocksPerFIle"
End If
intprog = intprog + 1
Forms!frm_progress!prog.Caption = "Search for Bad Strings in " & tblName & " Is Processing " & intprog & " records of " & rs1.RecordCount
Pause 0.01
For i = 1 To rs1.Fields.Count - 2
strNewString = RemoveBadChar(rs1.Fields(i).Value)
If strNewString <> rs1.Fields(i).Value Then
rs1.Fields(i).Value = strNewString
Else
'do nothing
End If
Next
rs1.MoveNext
Loop
rs1.Close
Set rs1 = Nothing
DoCmd.Close acForm, "frm_progress"
End Function
It does take some time so I have a progress form that keeps me occupied while I wait. You can always take that out. While I am sure this is not the cleanest or simplest way to do it. It works great for me.
Have fun!:banghead: