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:
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