Data Validation using VBA

[Cheshire101]

I am looking for help on the code that will test data entered into a field on a subform for the alphanumeric format (probably a bad word to use, but cannot think of a better one) entered.



I am trying to use something simlar to a validation rule but the requirements can change based on other fields that are completed.

I can get all of the code to work with the exception of the actual test.

I have a table that keeps the validation required: Example
AA###AAA###
A is a required letter and # is a required number

Does anyone know a way I can test if the data entered in a specific field matches this format?

Thank you all

[ssanfu]

There are many ways to check for numbers/letters. Here is my way:

Public Function ValidateField(pTheField As String) As Boolean
   Dim tmp   'holds char
   Dim IsValid As Boolean
   Dim k As Integer  'counter

   ValidateField = False

   'format = AA###AAA###
   ' check length. must be 11 chars
   If Len(pTheField) = 11 Then
      For k = 1 To 11
         tmp = Mid(pTheField, k, 1)
         Select Case k
            Case 1, 2, 6, 7, 8
               IsValid = InStr("ABCDEFGHIJKLMNOPQRSTUVWXYZ", UCase(tmp))
               If Not IsValid Then
                  'MsgBox "not a letter at position " & k
                  Exit Function
               End If
            Case 3, 4, 5, 9, 10, 11
               If Not IsNumeric(tmp) Then
                  'MsgBox "not a number at position " & k
                  Exit Function
               End If
         End Select
      Next
      ValidateField = True
   End If
End Function

This function will return TRUE or FALSE.

Call it : IF ValidateField(Me.ProjectID) =TRUE then

[Cheshire101]

This helps, but my issue with the format in a variable format does not work in this case...
Let explain better...Example:
For record #1 the format could be AA###AAA##
For record #2 the format could be A##AAA####
For record #3 the format could be #####

I have not worked with case very much... so my question is can you set a variable to case number.

Can you run a loop to create whch case numbers would be applicable to the letter test and which would be applicable to the number

[Cheshire101]

I am posting this for anyone who might have a similar problem. I took the code from ssanfu (thank you very much) and added a check for what type of character is required and made other inputs variables.

Public Function ValidateField(pTheField As String, FormatCount As Integer, ActualFormat As String) As Boolean
 
   Dim tmp   'holds char
   Dim IsValid As Boolean
   Dim k As Integer  'counter
   Dim tmp1 'Holds char type
   Dim tmpX As Integer
   Dim tmpY As Integer
   Dim tmpZ As Integer
 
   ValidateField = False
 
   If Len(pTheField) = FormatCount Then
 
      tmpX = 0
      tmpY = 0
      tmpZ = 0
 
      For k = 1 To FormatCount
 
         tmp = Mid(pTheField, k, 1)
         tmp1 = Mid(ActualFormat, k, 1)
 
         If tmp1 = "#" Then tmpX = k
         If tmp1 = "A" Then tmpY = k
         If tmp1 = "-" Then tmpZ = k
 
         Select Case k
            Case tmpY
               IsValid = InStr("ABCDEFGHIJKLMNOPQRSTUVWXYZ", UCase(tmp))
               If Not IsValid Then
                  MsgBox pTheField & " should have a letter at position " & k
                  Exit Function
               End If
            Case tmpX
              If Not IsNumeric(tmp) Then
                  MsgBox pTheField & " should have a letter at position " & k
                  Exit Function
 
               End If
            Case tmpZ
               IsValid = InStr("-", tmp)
               If Not IsValid Then
                  MsgBox pTheField & " should have a hyphen at position " & k
                  Exit Function
               End If
 
        End Select
 
      Next
 
      ValidateField = True
 
   End If
 
End Function