Table validation that excludes chars in multiple positions within length.

[jsmain]

I am trying to add validation to my table field to prevent entry of certain characters.
I am entering Serial numbers and rev letter.

Serial numbers are 6-10 chars in length, and may include B,C,D,F,G,H,J,K,L,M,N,P,R,T,V,W,X,Y,0,1,2,3,4,5,6, 7,8,9, Excluding A,E,I,O,Q,S,U,Z
Revs may contain up to 2 chars, and exclude I,O,Q,S,X,Z, and 0-9

I have been able to determine how to implement a validation for a single char, but multiple eludes me.
What I've tried (Rev):

Like "[!0-9,I,O,Q,S,X,Z]"
Like "[!0-9,I,O,Q,S,X,Z][!0-9,I,O,Q,S,X,Z]"


Like "[!*0-9*,*I*,*O*,*Q*,*S*,*X*,*Z*][!*0-9*,*I*,*O*,*Q*,*S*,*X*,*Z*]"

Am I close? What am I missing???
From what I gather, each group in sq brackets is set to a char position, but how do I make those after the first optional?

[Micron]

Table field level validation is limited in many ways. That shouldn't matter since the best approach is to use forms and not edit/append in the actual tables. In fact, casual users shouldn't even see them. If you use forms and code, you can implement virtually any kind of multiple constraints.

[jsmain]

Ok, but that doesn't fix the problem, It still does the same after removing at the table level, and inserting at the form level. Are you suggesting at VBA code level (On Change event) of the form to filter the field content?

[Micron]

I'm talking about a procedure (code) written to handle the constraints and the variations. Seems like there could be quite a few of them, but believe it or not, that's not really evident yet. What I did was throw out a vague suggestion because I don't know if you're open to the suggestion. Some will say that they want a non-code solution for one reason or another, so I don't just jump off the diving board and start writing stuff as a rule. A code solution greatly depends on what the inputs look like. What you've shown so far is the constraints. In order to make use of any one of several vba functions, we'd have to know what the inputs look like. It's important that your samples (if you choose to provide them) cover all the bases. We'd have to know where to expect numbers vs letters for all possibilities, and whether or not they are or must be upper case only. That would be a start. Any code you use that might be provided should work for as many variations that you know of. For those you don't, it would likely fail so it is important to cover as many variations of the inputs as possible.

[jsmain]

I thought I was clear on the constrains....

Rev letters could be anything from A to YY (Length of 1 or 2 chars). I did not mention upper case, but they would be converted during entry regardless. Something I did not mention was it could also be a "-", so "-ABCDEFGHIJKLMNPRTUVWY" as acceptable in position 1, and the second, all except "-".

Serial numbers are a mix of letters and numbers with the previous requirements, like 4P2HNM or 1C811F50D3 (Length of 6 to 10 chars) as examples. I've done nothing with "Macros", but have done some VBA in Excel and Access.

[Micron]

constraints and the variations

Samples that would encompass the variations is what I think is needed.
I attended the funeral service of my best friend today, so if I seem a bit cranky it's because I'm trying to help here and there in an attempt to bury my grief. It's not really working...

[orange]

You could use the form BeforeUpdate and use a function to check your validation requirements before accepting the serial number

[jsmain]

Samples that would encompass the variations is what I think is needed.
I attended the funeral service of my best friend today, so if I seem a bit cranky it's because I'm trying to help here and there in an attempt to bury my grief. It's not really working...

Sorry to hear, and for your loss!

[Micron]

Thank you.
After reviewing your first post I'm thinking you don't care where alpha or numeric characters are located, you just don't want what you posted as exclusions to be anywhere in the string. Sorry if I missed that at first.

[ArviLaanemets]

A probable solution:
In BeforeUpdate event of control in form, cycle through the entry characterwise, and check for the ASCII code of every character being in allowed range(s). Something like

...
booStringIsOK = True
For i = 1 To Len(Me.YourString)
     intCharCode = Code(Upper(Mid(Me.YourString, i, 1)))
     booStringIsOK = booStringIsOK And ((intCharCode Between 48 And 57)  Or (intCharCode Between 65 And 68) Or ...)
Next i
If booStringIsOK = False Then
     <some code for false entry handling>
End If
...

[CJ_London]

I would have thought these functions would to the the trick

Function ValidSerial(s As String) As Boolean
Dim i As Integer
Dim L As Integer
     
    L = Len(s)
    If L < 6 Or L > 10 Then
         Exit Function 'validserial is false because not in required len range
     Else
         ValidSerial = True
         i = 1
         Do While ValidSerial = True
             ValidSerial = "BCDFGHJKLMNPRTVWXY0123456789" Like "*" & Mid(s, i, 1) & "*"
             i = i + 1
             If i > L Then Exit Do
         Loop
     End If
End Function


Function ValidRev(s As String) As Boolean
Dim i As Integer
Dim L As Integer
     
    L = Len(s)
    If L > 2 Then
         Exit Function 'validserial is false because not in required len range
     Else
         ValidRev = True
         i = 1
         Do While ValidRev = True
             ValidRev = "-ABCDFGHJKLMNPRTVWY" Like "*" & Mid(s, i, 1) & "*"
             i = i + 1
             If i > L Then Exit Do
         Loop
     End If
End Function

or a more general function

Function Validated(s As String, minL As Integer, maxL As Integer, OKchars As String) As Boolean
Dim i As Integer
Dim L As Integer
     
    L = Len(s)
    If L < minL Or L > maxL Then
         Exit Function 'validserial is false because not in required len range
     Else
         Validated = True
         i = 1
         Do While Validated = True
             Validated = OKchars Like "*" & Mid(s, i, 1) & "*"
             i = i + 1
             If i > L Then Exit Do
         Loop
     End If
End Function

[orange]

Based on the initial post, I created a function to check proposed serial numbers against your criteria.
Here is code for checking serial numbers and a test routine.You could use similar approach to deal with revisions.

' ----------------------------------------------------------------
' Procedure Name: checkSerial
' Purpose: Routine to check a string for length and excluded characters
'Serial numbers are 6-10 chars in length, and may include
'B,C,D,F,G,H,J,K,L,M,N,P,R,T,V,W,X,Y,0,1,2,3,4,5,6, 7,8,9,
'Excluding A,E,I,O,Q,S,U,Z
' Procedure Kind: Function
' Procedure Access: Public
' Parameter serNo (String): String/Serial number to check
' Return Type: String
' Author: Jack
' Date: 13-Mar-24
' ----------------------------------------------------------------
Function checkSerial(serNo As String) As String
          Dim i As Integer
          Dim ErrCondition As String
10        ErrCondition = ""
          'check for length
20        If (6 > Len(serNo) Or 10 < Len(serNo)) = True Then
30            ErrCondition = ErrCondition & " -invalid length; "
              ' Debug.Print serNo & "   has invalid length"     'for testing
40        End If
50        For i = 1 To Len(serNo)
              'check for excluded chars
60            If InStr("AEIOQSUZ", Mid(serNo, i, 1)) > 0 Then
70                ErrCondition = ErrCondition & Mid(serNo, i, 1) & ", "
                  ' Debug.Print Mid(serNo, i, 1) & "  is illegal character"  'for testing
80            Else
                  ' Debug.Print Mid(serNo, i, 1) & "  is OK" 'for testing
90            End If
100       Next i
         
110       If Len(ErrCondition & "") = 0 Then
120           checkSerial = UCase(serNo)
130       ElseIf InStr(ErrCondition, ",") > 0 Then
140           ErrCondition = ErrCondition & " -illegal character(s)"
150           checkSerial = ErrCondition
160       Else
170           checkSerial = ErrCondition
180       End If
End Function

Test routine

Sub TestCheckSerial()
          Dim i As Integer
          Dim serials(4) As String
10        serials(0) = "Cyrr888"
20        serials(1) = "Seri888"
30        serials(2) = "2lng8889998YOU33"
40        serials(3) = "iou"
50        serials(4) = "123456789"
60        For i = LBound(serials) To UBound(serials)
70            Debug.Print serials(i) & "   " & checkSerial(serials(i))
80        Next i
End Sub

Results of test

Cyrr888   CYRR888
Seri888   S, e, i,  -illegal character(s)
2lng8889998YOU33    -invalid length; O, U,  -illegal character(s)
iou    -invalid length; i, o, u,  -illegal character(s)
123456789   123456789

[jsmain]

Thank you all! When I get another free moment, I will give these suggestions a try.