Boolean Switch to test for specific character sets within a field. This codes tests whether a field contains blanks or the specified characters only and will return -1 if true and 0 if false. If a character occurs that is not within the LValid_Values it will return 0 as false. This is different from identifying whether a field contains the listed characters. This can be useful for identifying characters in a field that you are wanting to alter the variable type. MS Access (and other databases) will delete field values that cannot be converted so if possible you want to identify values with illegal characters. This code can be used to identify this. Change the value of LValid_Values to represent the allowable characters and then you can reference the function in a query to identify illegal records and values. My primary use case is testing for numerical values in a string field which I am looking to alter so that I can change it into a long integer variable type. This is particularly useful for hunting down things like letters in house numbers or slashes in flat identities.
Function CharCheck(targetField) As Boolean Dim LPos As Integer Dim LChar As String Dim LValid_Values As String 'Start at first character in strField LPos = 1 LValid_Values = ".0123456789" 'Test each character in strField While LPos <= Len(targetField) 'Single character in strField LChar = Mid(targetField, LPos, 1) 'If character is not LValid Value, return FALSE If InStr(LValid_Values, LChar) = 0 Then CharCheck = False Exit Function End If 'Increment counter LPos = LPos + 1 Wend 'Value is LValid Value, return TRUE CharCheck = True End Function