Data Validation using LIKE operator

[sra2786]

I want to validate user input in the BeforeUpdate so that I can create a custom message if the data is in the wrong format. I tried the following but it does not check for uppercase letters.


An example of a correct format is CATX12345. Can someone tell me how to check for uppercase letters?

blnIsValid = (strIn Like "[A-Z][A-Z][A-Z][A-Z]#####")

Thanks.
Sandy

[davegri]

You can ignore what the user inputs and force the entry to uppercase with the UCASE function:
strin=ucase(strin)

[Gicu]

Here is a function I use to check a password "complexity", you can see how I check for UCase, you can easily adapt it for your needs:

Public Function IsComplex(sInput As String) As Boolean


Dim i As Integer


If Len(sInput) > 8 Then
  IsComplex = True
End If


Test1:
'numeric character
If IsComplex Then
    For i = 1 To Len(sInput)
      If IsNumeric(Mid(sInput, i, 1)) Then
          GoTo Test2 'My apologies.
      End If
   Next
   IsComplex = False
End If


Test2:
'non-numeric character
If IsComplex Then
   For i = 1 To Len(sInput)
       If Not IsNumeric(sInput) Then
           GoTo Test3
       End If
   Next
   IsComplex = False
End If


Test3:
'Upper case
If IsComplex Then
    For i = 1 To Len(sInput)
      If StrComp(Mid(sInput, i, 1), LCase(Mid(sInput, i, 1)), vbBinaryCompare) <> 0 Then
          GoTo Test4
      End If
   Next
   IsComplex = False
End If


Test4:
'lower case
If IsComplex Then
   For i = 1 To Len(sInput)
       If StrComp(Mid(sInput, i, 1), LCase(Mid(sInput, i, 1)), vbBinaryCompare) = 0 Then
           GoTo ExitFunction
       End If
   Next
   IsComplex = False
End If




ExitFunction:
End Function

Cheers,
Vlad