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!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
you can use the replace function


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
those are chr(10)

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))
Close #1

End Sub
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

Open in new window

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
SELECT FieldName, instr(FieldName,Chr(10)) As DodgyLocation
FROM MyTable
WHERE instr(FieldName,Chr(10)) > 0
SET FieldName = Replace(FieldName,Chr(10),"")
WHERE instr(FieldName,Chr(10)) > 0

Open in new window

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
'MsgBox "Change OK"

Open in new window

joibrooksAuthor Commented:
i went for quick and simple. however, anyone with a similar problem will find that all these solutions offered valuable info.
Rey Obrero (Capricorn1)Commented:
the simplest solution is to run an update query

update TableX
set [myfield]=replace(replace(replace([myfield],chr(13),""),chr(10),""),chr(9),"")
joibrooksAuthor Commented:
absolutely, capricorn1. after my comment, i was closing the question but your fingers were ultimately faster than mine. thanks again.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.