Remove Strange Characters from Access Data Field
I have posted a similar problem to you experts, but here's a new spin. I get an access database from a client and each time i get it, there is always something challenging about it. Previously resolved was my question about removing various HTML tags from a field. Well, now I think that I'm battling carriage returns, line feeds, or some such strange character. I can see the character, I can copy it to the notepad, but I cannot paste it into a simple Find and Replace window or query for it. I've attached the character in a text file and would be more than thrilled to find out what the character is and how to replace it with something like a end of sentence period and space or simply remove it from my data.
I was trying an update query with "*" & chr(13) & "*". I tried charcode 10 and charcode 32 also, but none of these removed anything.
These special characters show up at the beginning of the field, in the middle of the field and at the end of the field. Pretty much anywhere in the field! Sometimes there is one of these characters, sometimes two.
Thank you!
strangecharacter.txt
I was trying an update query with "*" & chr(13) & "*". I tried charcode 10 and charcode 32 also, but none of these removed anything.
These special characters show up at the beginning of the field, in the middle of the field and at the end of the field. Pretty much anywhere in the field! Sometimes there is one of these characters, sometimes two.
Thank you!
strangecharacter.txt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you might want to start by finding a field with the data then run it in the code below.
Public Function SearchBadChars(ByVal inString As String) As String
Dim intCurLoc As Integer, CharValue As Integer
For intCurLoc = 0 To Len(inString) - 1
CharValue = Asc(Mid$(inString, intCurLoc, 1))
If CharValue < 32 Then
inString = Replace(inString, Chr(CharValue), " ")
End If
If CharValue > 126 Then
inString = Replace(inString, Chr(CharValue), " ")
End If
Next intCurLoc
SearchBadChars = inString
End Function
You may find it worthwhile knowing what you are replacing so you can query your table
I have used CHR(10) which is a line feed character but you will certainly want to check for all CHR's from CHR(0) to CHR(31) watch out for CHR(32) its a space.
Cheers, Andrew
I have used CHR(10) which is a line feed character but you will certainly want to check for all CHR's from CHR(0) to CHR(31) watch out for CHR(32) its a space.
Cheers, Andrew
SELECT FieldName, instr(FieldName,Chr(10)) As DodgyLocation
FROM MyTable
WHERE instr(FieldName,Chr(10)) > 0
UPDATE MyTable
SET FieldName = Replace(FieldName,Chr(10),"")
WHERE instr(FieldName,Chr(10)) > 0
I create scripts for different problem characters and call them in the program start with a shell and wait.
'=============================================================================================
'Script Name = FindAndReplace.vbs
'Windows Scripting must be enabled for this script to run
'This VB script searches a folder and files by specified type, and replaces every occurance of
' of that string in every file in that folder of that type
'=============================================================================================
Const ForReading = 1
Const ForWriting = 2
Set fso = Wscript.CreateObject("Scripting.FileSystemObject")
'Enter folder to search on the line below ==================================Enter Param Below
Set folder = fso.GetFolder("J:\Acct\IC_Data\Reports\ARTB\")
Set files = folder.files
'Supply text to search for on the line below ===============================Enter Param Below
varText = " "
For Each f1 In files
'Select file type to search and replace below ==============================Enter Param Below
If InStr(f1,".txt") > 0 Then
Set f = fso.OpenTextFile(f1,ForReading)
If Not f.AtEndOfStream Then
'Supply text to replace with on the line below =============================Enter Param Below
varNew = Replace(f.ReadAll,varText,"")
Set f = fso.OpenTextFile(f1,ForWriting )
f.Write varNew
End If
Set f = Nothing
End If
Next
'MsgBox "Change OK"ASKER
i went for quick and simple. however, anyone with a similar problem will find that all these solutions offered valuable info.
the simplest solution is to run an update query
update TableX
set [myfield]=replace(replace(
update TableX
set [myfield]=replace(replace(
ASKER
absolutely, capricorn1. after my comment, i was closing the question but your fingers were ultimately faster than mine. thanks again.
use this codes to find what the ascii value of the char
Sub chkChar()
Dim s, j
Open "C:\strangecharacter.txt" For Input As #1
Do Until EOF(1)
Line Input #1, s
For j = 1 To Len(s)
Debug.Print Asc(mid(s, j, 1))
Next
Loop
Close #1
End Sub