VBA Function Boolean Switch to test for specific character sets within a field

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    

Author: Mark

Mark Brooks a forty something individual working and living in and around Edinburgh