How to I remove nonprintable character from a text field in MS Access (2003)?
I found the CLEAN function that works in Excel, but it is not available
Access.
Could you write a vba function like this:
Private Clean( sInputString as string ) as string
Dim nLen, nIndex as integer
Dim sOutputString as string
Dim sSingleChar as string
Dim nAsciiValue as integer
sOutputString = ""
nLen = Len( sInputString )
for nIndex = 1 to nLen
sSingleChar = Mid( sInputString, nIndex, 1 )
nAsciiValue = asc( sSingleChar )
if nAsciiValue < 32 or nAsciiValue > 128 then
' These are non-printable characters
else
sOutputString = sOutputString & sSingleChar
end if
next
Clean = sOutputString
end function
Then write an update statement like
Update table1
set column1 = Clean( column1 )
Would this work?