validate mixed text/number string

[dmeehanjr]

I need to validate an 8 charactor field that it contains:
[FONT=&quot] two letters, five numbers, one letter (in that order)

How could I best do that?
Thanks!

[/FONT]

 

[John Big Booty]

In the field's Before Update event you could try some code along the lines of;

[COLOR="DarkGreen"]'Test Length is OK[/COLOR]
If [URL="http://www.techonthenet.com/access/functions/string/len.php"]Len[/URL](Me.YourField) <> 8 Then
     MsgBox "Input must be eight characters long in the form Two Letters, Five Numbers and One Letter"
     Cancel = True
     Exit Sub
End If

Dim x As Integer

[COLOR="DarkGreen"]'Loop through each position and test for numeric or string[/COLOR]
For x = 1 to 8

Select Case x

[COLOR="DarkGreen"]'Test positions 1, 2, and 8 for string charterer[/COLOR]
Case 1, 2, 8
     If [URL="http://www.techonthenet.com/access/functions/advanced/isnumeric.php"]IsNumeric[/URL]([URL="http://www.techonthenet.com/access/functions/string/mid.php"]Mid[/URL](Me.YourField, x, 1)) = True Then
          MsgBox "Charterer " & x & " must be a Letter between A to Z"
          Cancel = True
          Exit Sub
     End If
[COLOR="DarkGreen"]
'Test all other positions for numeric charterer[/COLOR]
Case Else
     If IsNumeric(Mid(Me.YourField, x, 1)) = False Then
          MsgBox "Charterer " & x & " Must be Numeric"
          Cancel = True
          Exit Sub
     End If
End Select

Next x

 

[pr2-eugin]

Hello JBB, your code is far more better than what I was planning to give.. Thankfully you came in with the code before I pressed Post Quick Reply.. However.. Just one little suggestion.. Could the Case statements under 1,2,8 be replaced with this?

[B]:[/B]
Select Case x

[COLOR=Green]'Test positions 1, 2, and 8 for string charterer[/COLOR]
Case 1, 2, 8
    [B]IsText[/B](Mid(Me.YourField, x, 1))[B] = False[/B]
[B]:
:[/B]

Where IsText is a User defined Function..

Public Function IsText(inputStr As String) As Boolean
[COLOR=Green]' Checks the incoming Letter; if it finds itself in the ASCII table[/COLOR]
[COLOR=Green]' Produces Output TRUE or FALSE based on the incoming letter
' This avoid special characters being treated as String
[/COLOR]    IsText = IIf((Asc(inputStr) >= 65 And Asc(inputStr) <=  90) Or (Asc(inputStr) >= 97 And Asc(inputStr) <= 122), True,  False)
End Function

Just because the code would recognize "-)12345#" as a valid entry, while it should have said..
"Charterer 1 must be a Letter between A to Z" ??

 

[John Big Booty]

Yes I was just playing with that contingency, and came up with;

[COLOR="DarkGreen"]'Test Length is OK[/COLOR]
If Len(Me.TestString) <> 8 Then
     MsgBox "Input must be eight characters long in the form Two Letters, Five Numbers and One Letter"
     Cancel = True
     Exit Sub
End If

Dim x As Integer
[COLOR="DarkGreen"]
'Loop through each position and test for numeric or string[/COLOR]
For x = 1 To 8

Select Case x

[COLOR="DarkGreen"]'Test positions 1, 2, and 8 for string charterer[/COLOR]
Case 1, 2, 8
     If IsNumeric(Mid(Me.TestString, x, 1)) = False Then
        If Asc(Mid(Me.TestString, x, 1)) < 65 Or Asc(Mid(Me.TestString, x, 1)) > 90 Then
            If Asc(Mid(Me.TestString, x, 1)) < 97 Or Asc(Mid(Me.TestString, x, 1)) > 122 Then
                MsgBox "Charterer " & x & " must be a Letter between A to Z"
                Cancel = True
                Me.TestString.SelStart = x - 1
                Me.TestString.SelLength = 1
                Exit Sub
            End If
        End If
     End If
[COLOR="DarkGreen"]
'Test all other positions for numeric chartere[/COLOR]r
Case Else
     If IsNumeric(Mid(Me.TestString, x, 1)) = False Then
          MsgBox "Charterer " & x & " Must be Numeric"
          Cancel = True
          Me.TestString.SelStart = x - 1
          Me.TestString.SelLength = 1
          Exit Sub
     End If
End Select

Next x

Possibly your solution is even more elegant than what I have developed

 

[John Big Booty]

I've also added some code to select any characters that don't fit the required format.

 

[pr2-eugin]

I've also added some code to select any characters that don't fit the required format.

Nice..