Search for the Square Boxes 1

[alr0]

Hi All,

We have been receiving data for mailing lists in Excel spreadsheets that have a line feed or carriage returns (chr 10 or 13, I believe) When imported into Access the character appears as a square box like many unprintable or foreign characters. I need to alert operators to the presence of characters that require some special handling.

Is there a way that I can search for these square boxes whatever their source.

Thanks,

alr

_____________________________________
There is no rule more invariable than that we are paid for our suspicions by finding out what we expected.

Henry David Thoreau

 

[Remou]

A query may suit:

[tt]SELECT t.Address
FROM tblTable t
WHERE t.Address Like "*" & Chr(10) & "*"
OR t.Address Like "*" & Chr(13) & "*"[/tt]

 

[alr0]

Thanks Remou,

This is very helpful.

I was hoping for a more general solution that would find all characters that show up as square boxes without listing each ascii character.

Any thoughts?

Thanks,

alr

_____________________________________
There is no rule more invariable than that we are paid for our suspicions by finding out what we expected.

Henry David Thoreau

 

[Remou]

I think you would need a function. For example:

Sub OddChar()
Dim intA
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("tblX")

Do While Not rs.EOF
    
    strText = ""
    
    For i = 1 To Len(rs!testfield)
        intA = Asc(Mid(rs!testfield, i, 1))
        Debug.Print intA
        If intA < 32 Or intA > 166 Or intA = 127 Or intA = 129 _
            Or intA = 141 Or intA = 143 Or intA = 144 Or intA = 157 Then
            strText = strText & " "
        Else
            strText = strText & Mid(rs!testfield, i, 1)
        End If
    Next
    rs.Edit
    rs!testfield = strText
    rs.Update
    rs.MoveNext
Loop

End Sub