Validation Rules: Letter and Number Validation

[bluray]

Hello all, and thanks for taking the time to help me out here.

Basically, I am trying to set up a validation rule that correlates with an input mask where the user is required to enter select alpha characters followed by a series of numeric characters.

The input mask Auto-enters API, requires an alpha value in upper case, and up to 8 numeric values., as seen here:

"API"<L99999999

Now, the part I am having trouble with is setting up the validation rule. I need for it to check and make sure that either "M" or "A" have been entered following the auto entered "API". The validation rule (as seen below) will do that, but no matter how much I mess with it I can't seem to get it to allow between 1 and 8 numeric values to be entered following the "M" or "A". Values like APIA1234 (or APIM1234) should be accepted just as APIA12345678 (or API12345678) would.

Here is the validation rule so far:
"API" & "" & "M" Or "A"

Again, thanks guys!

[puppydogbuddy]

Hello all, and thanks for taking the time to help me out here.

Basically, I am trying to set up a validation rule that correlates with an input mask where the user is required to enter select alpha characters followed by a series of numeric characters.

The input mask Auto-enters API, requires an alpha value in upper case, and up to 8 numeric values., as seen here:

"API"<L99999999

Now, the part I am having trouble with is setting up the validation rule. I need for it to check and make sure that either "M" or "A" have been entered following the auto entered "API". The validation rule (as seen below) will do that, but no matter how much I mess with it I can't seem to get it to allow between 1 and 8 numeric values to be entered following the "M" or "A". Values like APIA1234 (or APIM1234) should be accepted just as APIA12345678 (or API12345678) would.

Here is the validation rule so far:
"API" & "" & "M" Or "A"

Again, thanks guys!

Your validation rule says that the field value can be "API M" or "A"

Try this:
Left([Field], 4) = "APIM" or Left([Field],4) = "APIA"

Replace [Field] with the name of the field the validation rule applies to.

[ADezii]

Hello all, and thanks for taking the time to help me out here.

Basically, I am trying to set up a validation rule that correlates with an input mask where the user is required to enter select alpha characters followed by a series of numeric characters.

The input mask Auto-enters API, requires an alpha value in upper case, and up to 8 numeric values., as seen here:

"API"<L99999999

Now, the part I am having trouble with is setting up the validation rule. I need for it to check and make sure that either "M" or "A" have been entered following the auto entered "API". The validation rule (as seen below) will do that, but no matter how much I mess with it I can't seem to get it to allow between 1 and 8 numeric values to be entered following the "M" or "A". Values like APIA1234 (or APIM1234) should be accepted just as APIA12345678 (or API12345678) would.

Here is the validation rule so far:
"API" & "" & "M" Or "A"

Again, thanks guys!

To check if:

1. The entry begins with API.
2. The 4th character is either an A or M, nothing else.
3. There is a minimum of 1 and a maximum of 8 digits following the APIA/M.
4. All values after the 4th character are Numeric in data type.
   

   Private Sub txtTest_BeforeUpdate(Cancel As Integer)
   Dim strValueEntered As String, intCounter As Integer
    
   strValueEntered = Me![txtTest]
    
   If Len(strValueEntered) = 0 Or Left$(strValueEntered, 3) <> "API" Then
     MsgBox "Invalid Entry"
       Cancel = True
       Exit Sub
   End If
    
   'Does it pass the A & M test?
   Select Case Mid$(strValueEntered, 4, 1)
     Case "A", "M"
     Case Else
       MsgBox "Invalid Entry"
         Cancel = True
         Exit Sub
   End Select
    
   'Does it pass the 1 to 8 and Numeric test, namely 1 to 8 digits
   'after the APIM or APIA?
   If Len(strValueEntered) >= 5 And Len(strValueEntered) <= 12 Then
     For intCounter = 5 To Len(strValueEntered)
       If Not IsNumeric(Mid$(strValueEntered, intCounter, 1)) Then
         MsgBox "Invalid Entry"
           Cancel = True
           Exit Sub
       End If
     Next
   Else
     MsgBox "Invalid Entry"
       Cancel = True
       Exit Sub
   End If
    
   'IF YOU EVENTUALLY GET HERE, ALL CONDITIONS WERE MET!
   End Sub

[bluray]

oh wow thanks guys! Both excellent points :) I will test them out right away here and let you guys know how it works out.

[ADezii]

oh wow thanks guys! Both excellent points :) I will test them out right away here and let you guys know how it works out.

It seems like you will have to go the VBA route on this one. It would be very difficult to incorporate your 4 Validations Requests into a single Validation Rule that would be all encompassing.

[bluray]

It seems like you will have to go the VBA route on this one. It would be very difficult to incorporate your 4 Validations Requests into a single Validation Rule that would be all encompassing.

I agree. However puppydogbuddy is right about how I botched the original rule I had in mind... can't wait until I reach the Zen level of designing databases you guys are at lol

[bluray]

ok guys, so I have now implemented the script provided by ADezii and its works great. I only had to tweak a few things to make it fit.

The first thing was of course swapping out the "txttest" control name fillers for the actual names of my controls.

The second thing I had to do was add ;0;_ onto the end of my input mask so it recorded the mask data into the table as well as the user entered data. Otherwise the VB script can't find the "API" portion of the data in the table (because it hasn't been entered and does not exist) and kicks back error messages.

Again thanks all for the help!