By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,030 Members | 2,277 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,030 IT Pros & Developers. It's quick & easy.

Validation Rules: Letter and Number Validation

P: 14
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!
Jun 21 '07 #1
Share this Question
Share on Google+
6 Replies


puppydogbuddy
Expert 100+
P: 1,923
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.
Jun 21 '07 #2

ADezii
Expert 5K+
P: 8,638
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.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub txtTest_BeforeUpdate(Cancel As Integer)
    2. Dim strValueEntered As String, intCounter As Integer
    3.  
    4. strValueEntered = Me![txtTest]
    5.  
    6. If Len(strValueEntered) = 0 Or Left$(strValueEntered, 3) <> "API" Then
    7.   MsgBox "Invalid Entry"
    8.     Cancel = True
    9.     Exit Sub
    10. End If
    11.  
    12. 'Does it pass the A & M test?
    13. Select Case Mid$(strValueEntered, 4, 1)
    14.   Case "A", "M"
    15.   Case Else
    16.     MsgBox "Invalid Entry"
    17.       Cancel = True
    18.       Exit Sub
    19. End Select
    20.  
    21. 'Does it pass the 1 to 8 and Numeric test, namely 1 to 8 digits
    22. 'after the APIM or APIA?
    23. If Len(strValueEntered) >= 5 And Len(strValueEntered) <= 12 Then
    24.   For intCounter = 5 To Len(strValueEntered)
    25.     If Not IsNumeric(Mid$(strValueEntered, intCounter, 1)) Then
    26.       MsgBox "Invalid Entry"
    27.         Cancel = True
    28.         Exit Sub
    29.     End If
    30.   Next
    31. Else
    32.   MsgBox "Invalid Entry"
    33.     Cancel = True
    34.     Exit Sub
    35. End If
    36.  
    37. 'IF YOU EVENTUALLY GET HERE, ALL CONDITIONS WERE MET!
    38. End Sub
Jun 21 '07 #3

P: 14
oh wow thanks guys! Both excellent points :) I will test them out right away here and let you guys know how it works out.
Jun 21 '07 #4

ADezii
Expert 5K+
P: 8,638
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.
Jun 21 '07 #5

P: 14
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
Jun 22 '07 #6

P: 14
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!
Jun 28 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.