Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2012
    Posts
    25

    Unanswered: finding Bad characters

    I have a column in a table in MS Access. I am actually an sql server programmer.. So I have no knowledge about Ms access..! I got a requirement at my work place recently to fix or find those unwanted or unseen characters in the column fields.. This particular column that looks like a space is behaving very awkward. When I use back space to remove the space, it actually erases the last character that i have in the field. So I need to find out what that is.. I have written some code for the same problem in SQL. I tried to apply the same here but it shows errors.. Is there any way that i can find the bad characters??
    Any help is appreciated

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you know the ascii code of the character then user replace insode a SQL statement to remove it.
    depending on what the data is or where its come from its probably a crraieg return or a line feed

    if you know your data is only alpha numeric you could use the ASC function to find characters that are outside the required range
    MS Access: Asc Function
    or encode an asci value into its symbol
    MS Access: Chr Function

    ASCII character chart - Access - Office.com

    however it may! be a performance problem depedning onm the amount of rows and size of columns.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2012
    Posts
    25
    ok i found that its the bad character with ascii value 63.. I also found out its a ? sign. But its not what i see.. I see some space.. How ever if i use <quote>select * from tablename where Asc(Mid(columnname,5,1)) = 63</quote> It says invalid procedure call

  4. #4
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    try Mid(columnname,5,1) = chr(63)

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So if you know its ascii character 63....
    Update mytable set mycolumn = replace (mycolumn, chr$(63),'')
    As to how long that will take.....
    If you can devise a suitabile where clause so much the better

    Have you worked out where the odd symbols are coming from and how
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    can we see an example of your corrupted data?
    better yet stuff it into an Access mdb, zip it and post it here
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Oct 2012
    Posts
    25
    Quote Originally Posted by rogue View Post
    try Mid(columnname,5,1) = chr(63)
    Thanks for the reply... Can you suggest me how to apply the same on entire column.. like
    declare Position int
    while Position < length of the field
    begin
    select * from Members where Mid(columnname,Position,1) = chr(63)
    end
    Thanks Again

  8. #8
    Join Date
    Oct 2012
    Posts
    25
    Quote Originally Posted by healdem View Post
    So if you know its ascii character 63....
    Update mytable set mycolumn = replace (mycolumn, chr$(63),'')
    As to how long that will take.....
    If you can devise a suitabile where clause so much the better

    Have you worked out where the odd symbols are coming from and how
    its not exactly the ascii value 63 because i coded like below intending to replace the chr(63). I wont respond
    SELECT Replace(Lastname,Chr(63),'m') AS Expr1, Members.lastname, Len(lastname) AS length, Asc(Mid(LastName,1,1)) AS Expr3, Asc(Mid(LastName,2,1)) AS Expr4, Asc(Mid(LastName,3,1)) AS Expr5, Asc(Mid(LastName,4,1)) AS Expr6, Asc(Mid(LastName,5,1)) AS Expr7, Asc(Mid(LastName,6,1)) AS Expr8
    FROM Members where MemberID = 1345;
    I run the above query it still give the below output
    Expr1 lastname length Expr3 Expr4 Expr5 Expr6 Expr7 Expr8
    Cese–a Cese–a 6 67 101 115 101 63 97

  9. #9
    Join Date
    Oct 2012
    Posts
    25
    @ above

    Here it shows atleast - in the middle of casea but if i paste in word it wont show any thing... I ran the same above select statement for ASCII values 0 to 64 and 127 to 255.. It shows no change.. I am just puzzled

  10. #10
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    can you post some sample data?

    your best bet is healdem's suggestion to run an update query like

    Update mytable set mycolumn = replace (mycolumn, chr$(63),'')

    replacing mytable with your table name and mycolumn with the name of the offending column

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you are the person asserting its the question mark symbol ? or Ascii 63, not me
    as you haven't posted the data (inside a zipped mdb) then its impossible to diahgnoisze what isn't working

    just posting
    I run the above query it still give the below output
    doens't provide anything for us to help you with
    we don't know what the data is
    we don't know what it should be

    its quite possible its a non printing ascii character but until you know which one(s) there is stuff all we can do

    if you are unable to compact & repair the db, then zip it and post it here then you need to do some detective work and work out what rogue symbol is being displayed
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what you could do is create a form, set the source to be the table with the problem data
    plonk whatever controls you feel you need
    add a list box, being dull lets call it MyListBox
    right click the form, select 'build code'.

    in the forms on current event place
    ..say the control with the bad symbols is called MyWord
    Code:
    Private Sub Form_Current()
    If Len(MyWord) > 0 Then
      AnalyseWord (MyWord)
    End If
    End Sub
    then add the function Analsyeword tot he code
    Code:
    Private Sub AnalyseWord(ThisWord As String)
    While mylistbox.ListCount > 0
        mylistbox.RemoveItem (0)
    Wend
    If Len(Word) > 0 Then
      Dim intNoSymbols As Integer
      Dim intPointer As Integer
      Dim strOutput As String
      intNoSymbols = Len(ThisWord) 'find out how many characters to process
      For intPointer = 1 To intNoSymbols 'run throuygh each character and add it to the list box
        mylistbox.AddItem ("Character: #" & Format(intPointer, "#,##0") & "(" & Mid$(ThisWord, intPointer, 1) & ") is ASCII:" & Asc(Mid$(ThisWord, intPointer, 1)))
        
      Next intPointer
    Else ' don't do anythign, we have already cleeared the list box
    End If
    End Sub
    that will then analyse each character in the specified column
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •