none
Creating a Validation Rule That Requires A Range of Text and to Force Capital Letters RRS feed

  • Question

  • I would like to create 2 validation rules for 2 separate fields

    The first field is I want to force only the following Values to be entered:

    G6 or G7 or G8 0r G9 or G10

    The validation rule I came up with is: Like "G6" Or "G7" Or "G8" Or "G9" Or "G10"

    Is there another way for me to do this rule?

    The 2nd field is I want to force either a capital M or a capital F. I don't know how to set up a rule for capital letters.

    When I use : Like "M" Or "F, it still allows lower case.

    Plus is there a site to learn the syntax I can use in validation rules property?

    Keith


    Keith Aul

    Sunday, August 28, 2016 12:53 AM

Answers

  • Why do you want to use a validation rule? You could make those fields lookup the value in a list of values (or in another table for more flexibility)

    For the second field: if the Row Source is M;F, lower case m or f will automatically be converted to M or F.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, August 28, 2016 11:27 AM
  • I tried the ASC("M") in the validation rule field properties and its not working. I've tried Like ASC("M") or =ASC("M") and nothing works. Would that be the correct syntax?

    As for the first rule, it seems kinda long to input this expression in. there has to be a better method to check for these values since the common thing between all of them is the letter 'G' and I'm only checking for the numbers after the letter 6 thru 10



    Keith Aul

    Hi Keith. I just tried using Asc(), and it works for me. I used the following rule:

    Asc([FieldName])=70 Or Asc([FieldName])=77

    Hope it helps...

    PS. Oh, and for the first rule, I used this:

    Like "G[5-9]" Or "G10"

    Cheers!

    Sunday, August 28, 2016 2:59 PM
  • Hi Keith. The expression I gave you is called a Regular Expression or RegEx. Access can use simple RegEx with the Like operator. The characters with the brackets is called a "character class." It's not counting 5 though 9; rather, it's matching digits 5 through 9. So, since there is no digit 10, we have to separate it. Hope it helps...
    • Marked as answer by KeithAul Monday, August 29, 2016 2:03 AM
    Monday, August 29, 2016 1:52 AM

All replies

  • Hi Keith. What's wrong with the first rule? Why are you looking for a short one? There are other ways to do it, but I was just curious why you need it. For the second rule, see if you can use the Asc() function.
    Sunday, August 28, 2016 2:33 AM
  • I tried the ASC("M") in the validation rule field properties and its not working. I've tried Like ASC("M") or =ASC("M") and nothing works. Would that be the correct syntax?

    As for the first rule, it seems kinda long to input this expression in. there has to be a better method to check for these values since the common thing between all of them is the letter 'G' and I'm only checking for the numbers after the letter 6 thru 10



    Keith Aul

    Sunday, August 28, 2016 10:53 AM
  • Why do you want to use a validation rule? You could make those fields lookup the value in a list of values (or in another table for more flexibility)

    For the second field: if the Row Source is M;F, lower case m or f will automatically be converted to M or F.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, August 28, 2016 11:27 AM
  • I tried the ASC("M") in the validation rule field properties and its not working. I've tried Like ASC("M") or =ASC("M") and nothing works. Would that be the correct syntax?

    As for the first rule, it seems kinda long to input this expression in. there has to be a better method to check for these values since the common thing between all of them is the letter 'G' and I'm only checking for the numbers after the letter 6 thru 10



    Keith Aul

    Hi Keith. If Asc() didn't work, try using Chr().
    Sunday, August 28, 2016 1:33 PM
  • I tried the ASC("M") in the validation rule field properties and its not working. I've tried Like ASC("M") or =ASC("M") and nothing works. Would that be the correct syntax?

    As for the first rule, it seems kinda long to input this expression in. there has to be a better method to check for these values since the common thing between all of them is the letter 'G' and I'm only checking for the numbers after the letter 6 thru 10



    Keith Aul

    Hi Keith. I just tried using Asc(), and it works for me. I used the following rule:

    Asc([FieldName])=70 Or Asc([FieldName])=77

    Hope it helps...

    PS. Oh, and for the first rule, I used this:

    Like "G[5-9]" Or "G10"

    Cheers!

    Sunday, August 28, 2016 2:59 PM
  • I wanted a validation rule because I want to learn the various ways I can control what data is entered.

    Your solution works and I thought about doing this before, but I wanted to know how I can do the same thing using a validation rule. 

    Everyone: please post sources(books, sites, tutorials, ) that will teach us how to use the validation rule property and examples of the syntax that teach us various ways to control what data is entered.


    Keith Aul

    Monday, August 29, 2016 1:34 AM
  • Thanks this works but I have some questions.

    Why doesn't this work: Like "G[5-10]"

    Why do I have to separate "G10" from the expression?

    Also, do you know of sources of how I can learn why type of expressions are allowed in the validation rule property? I want to know what the brackets mean and what goes in between them plus all the other expressions that are allowed


    Keith Aul

    Monday, August 29, 2016 1:39 AM
  • Hi Keith. The expression I gave you is called a Regular Expression or RegEx. Access can use simple RegEx with the Like operator. The characters with the brackets is called a "character class." It's not counting 5 though 9; rather, it's matching digits 5 through 9. So, since there is no digit 10, we have to separate it. Hope it helps...
    • Marked as answer by KeithAul Monday, August 29, 2016 2:03 AM
    Monday, August 29, 2016 1:52 AM
  • Yes this helps to clarify you solution. But where can I learn how to write 'Regular Expression'?

    I'm sure there are multiple Regular Expression formats and I would like to learn about them. Thanks


    Keith Aul

    Monday, August 29, 2016 2:05 AM
  • Hi Keith. I talked a little bit about Regular Expressions in this blog. Within the code I posted, there's a website URL where you can learn more about them. Hope it helps...
    Monday, August 29, 2016 2:48 AM