505.369.1650 [email protected]

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, ",")

    On Error Resume Next
    Exit Function
    MsgBox Err.Number & " " & Err.Description, vbCritical, "AsciiCodeList()"
    AsciiCodeList = Null
    Resume Exit_Proc
End Function