When manually copying data from a document and pasting into a Microsoft Access data entry form, it is possible to accidentally include unwanted characters, like Tab or other hidden characters. This may make the data hard to retrieve in a query, and a Find or Search may not find the data. In a report, it may make some of the value wrap below the height of the display control so that the results look blank or incomplete.
Each character in a value, including hidden characters, may be represented by an Ascii decimal code. For example, the code for Tab is 9. If you have a way to examine each character in a value, you can determine whether or not the value has unwanted characters.
The VBA function below is a way to get a comma-separated list of Ascii codes that make up a value. It could be modified to look for specific characters and then tell the user that there is a problem by calling the function in the Before Update event procedure for a text box. If you prefer, you could modify the logic to automatically remove the unwanted characters.
You may see the results of the function in the Immediate window by calling it like this:
?AsciiCodeList("the value")
Public Function AsciiCodeList(TheValue)
' This procedure passes back a comma-separated list of Ascii codes for
' the characters in TheValue. It passes back a Null if there is no
' value or there is an error.
' AsciiCodeList() Version 1.0.0
' Copyright © 2023 Extra Mile Data, www.extramiledata.com.
' For questions or issues, please contact [email protected].
' Use (at your own risk) and modify freely as long as proper credit is given.
On Error GoTo Err_Handler
Dim avarCharacters()
Dim intChar As Integer
If IsNull(TheValue) Then
AsciiCodeList = Null
GoTo Exit_Proc
End If
ReDim avarCharacters(Len(TheValue) - 1)
For intChar = 1 To Len(TheValue)
avarCharacters(intChar - 1) = Asc(Mid(TheValue, intChar, 1))
Next intChar
AsciiCodeList = Join(avarCharacters, ",")
Exit_Proc:
On Error Resume Next
Exit Function
Err_Handler:
MsgBox Err.Number & " " & Err.Description, vbCritical, "AsciiCodeList()"
AsciiCodeList = Null
Resume Exit_Proc
End Function