[Webinar] Streamline your web hosting managementRegister Today


Remove Strange Characters from Access Data Field

Posted on 2008-02-05
Medium Priority
Last Modified: 2011-10-19
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!
Question by:joibrooks
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 2000 total points
ID: 20823549
you can use the replace function

LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20823605
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
LVL 14

Expert Comment

ID: 20823625
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

LVL 28

Expert Comment

ID: 20823671
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

LVL 18

Expert Comment

ID: 20823676
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


Author Comment

ID: 20824397
i went for quick and simple. however, anyone with a similar problem will find that all these solutions offered valuable info.
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20824436
the simplest solution is to run an update query

update TableX
set [myfield]=replace(replace(replace([myfield],chr(13),""),chr(10),""),chr(9),"")

Author Comment

ID: 20824499
absolutely, capricorn1. after my comment, i was closing the question but your fingers were ultimately faster than mine. thanks again.

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question