String Validations

[iglobalusa]

What string function, or series of string functions, in a code can I use to prevent a user from :

1) entering a leading 0 in a text field for an ID Code with 10 characters e.g. 0346788692
2) allowing only to enter a leading A or H and no other alpha e.g. A346788692
3) allowing only to enter an L. or P, or LB following a leading 2 and no other alpha or alpha combination e.g. 2L46788692 or 2LB6788692
4) allowing an ending X or N and no other alpha e.g. 346788692X
5) allowing a combination of numbers and alpha but not all alpha e.g.
A34678869X

Thanks for your expert assistance.

 

[bob fitz]

Do you want to apply ALL of these validations to a single text box or is each validation to be applied to a separate text box.

 

[iglobalusa]

To one text field, named stateno, with a length of 10 characters.

Thanks.

 

[bob fitz]

Try this:

Private Sub stateno_BeforeUpdate(Cancel As Integer)
  Dim i As Integer
  Dim blnNumFound As Boolean
 
  For i = 1 To 10
    If IsNumeric(Mid(Me.stateno, i, 1)) Then
      blnNumFound = True
      Exit For
    End If
    i = i + 1
    Debug.Print Mid(Me.stateno, i, 1)
  Next
 
  If blnNumFound = False Then
    MsgBox "Invalid Entry condition5: "
    Cancel = True
    Exit Sub
  End If
 
  If Len(Me.stateno) <> 10 Then 'condition0
    MsgBox "Invalid Entry condition0"
    Cancel = True
    Exit Sub
  End If
  If Left(Me.stateno, 1) = "0" Then 'condition1
    MsgBox "Invalid Entry condition1"
    Cancel = True
    Exit Sub
  End If
 
  If Not IsNumeric(Left(Me.stateno, 1)) And Left(Me.stateno, 1) <> "A" And Left(Me.stateno, 1) <> "H" Then 'condition2
    MsgBox "Invalid Entry condition2"
    Cancel = True
    Exit Sub
  End If
 
  If Not IsNumeric(Right(Me.stateno, 1)) And Right(Me.stateno, 1) <> "X" And Right(Me.stateno, 1) <> "N" Then 'condition4
    MsgBox "Invalid Entry condition4"
    Cancel = True
    Exit Sub
  End If
 
  If Left(Me.stateno, 1) = 2 Then 'condition3
    If (Left(Me.stateno, 3) <> "2LB" And Not IsNumeric(Right(Me.stateno, 7))) And (Left(Me.stateno, 2) <> "2L" Or Left(Me.stateno, 2) <> "2P" And Not IsNumeric(Right(Me.stateno, 8))) Then
        MsgBox "Invalid Entry condition3: "
        Cancel = True
    End If
  End If
End Sub

 

[iglobalusa]

Bob Fitzpatrick you're a wizard!

 

[boblarson]

Just thought I would chime in with a modified version of Bob's code. This version will notify the user of ALL non-conforming conditions at one time so that the user doesn't make more than one mistake and then fix one but not the other and then it does it to him again and again, which could be frustrating for them. This code sets a flag if anything is incorrect and then it builds a message string to tell them which condition is wrong and displays one message with all incorrect entry problems.

Private Sub stateno_BeforeUpdate(Cancel As Integer)
    Dim i As Integer
    Dim blnNumFound As Boolean
    Dim blnError As Boolean
    Dim strMsg As String
 
    For i = 1 To 10
        If IsNumeric(Mid(Me.stateno, i, 1)) Then
            blnNumFound = True
            Exit For
        End If
        i = i + 1
        Debug.Print Mid(Me.stateno, i, 1)
    Next
 
    If blnNumFound = False Then
        blnError = True
        strMsg = "Invalid Entry condition5: " & vbCrLf
    End If
    If Len(Me.stateno) <> 10 Then    'condition0
        blnError = True
        strMsg = strMsg & "Invalid Entry condition0" & vbCrLf
    End If
 
    If Left(Me.stateno, 1) = "0" Then    'condition1
        blnError = True
        strMsg = strMsg & "Invalid Entry condition1" & vbCrLf
    End If
 
    If Not IsNumeric(Left(Me.stateno, 1)) And Left(Me.stateno, 1) <> "A" And Left(Me.stateno, 1) <> "H" Then    'condition2
        blnError = True
        strMsg = "Invalid Entry condition2" & vbCrLf
    End If
 
    If Not IsNumeric(Right(Me.stateno, 1)) And Right(Me.stateno, 1) <> "X" And Right(Me.stateno, 1) <> "N" Then    'condition4
        blnError = True
        strMsg = strMsg & "Invalid Entry condition4" & vbCrLf
    End If
 
    If Left(Me.stateno, 1) = 2 Then    'condition3
        If (Left(Me.stateno, 3) <> "2LB" And Not IsNumeric(Right(Me.stateno, 7))) And (Left(Me.stateno, 2) <> "2L" Or Left(Me.stateno, 2) <> "2P" And Not IsNumeric(Right(Me.stateno, 8))) Then
            blnError = True
            strMsg = strMsg & "Invalid Entry condition3: "
        End If
    End If
    
    If blnError Then
        Cancel = True
        MsgBox "There are errors in your entry.  Please fix and retry.", vbInformation, "Entry Error"
    End If
End Sub

 

[bob fitz]

Bob Fitzpatrick you're a wizard!

A wizard? Perhaps I should change my user name to Harry Potter!

But seriously, I'm glad if it helped. Good luck with your endeavours.

 

[iglobalusa]

Bob Larson, many thanks for the code enhancement!