Validation Rules
By Richard Rost
4 years ago
Using Validation Rules to Set Limits for Data Entry
In this Microsoft Access tutorial, I will teach you how to use validation rules in to set limits for data entry in various types of fields. We'll look at validation rules for numeric, currency, text, and date field types. We'll see how to use validation text.
Sample Usage
- <5000
- >=0 AND <=5000
- Between 0 and 5000
- Like "R*"
- <#1972-10-23#
- <=Date()
- <=Date()-30
Recommended Course
Links
Learn More
 Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, validation rules, validation text, currency, number, text, string, numeric, date, date function
Intro In this video, we'll talk about how to use validation rules in Microsoft Access to control what kind of data users can enter in your tables and forms. You'll see examples like setting a maximum credit limit, allowing only specific numeric or text values, and creating rules for date fields, including options for relative dates based on today's date. We'll also cover how to create user-friendly error messages when someone enters something invalid. This is a Fast Tips video.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor Richard Rost. In today's video, we're going to talk about validation rules, and how to use validation rules in Microsoft Access to set limits for data entry.
So, you have a database and a customer form in that database, and you have something called a credit limit that you can set for each of your customers. All of a sudden, one of your employees comes along and does this: now I've got a $5 million credit limit. Personally, I don't see any problem with that. I'm trustworthy. But you might not want to let them do that. So how do we prevent that?
You could do it with programming and all kinds of complicated stuff, but there's a really easy way to set something called a validation rule in your table, or right in the form, either one, to prevent your users from typing in stuff like that. Let's take a look at how it works.
First, I'm going to hit escape because I'm not going to do that. 5,000 is fine. Let's close that form and go to the customer table, design view. For each of the fields in your table, for most of them, you're going to have something called a validation rule.
Let's click on credit limit, and right down here, you see validation rule and validation text. The validation rule is an expression you can type in, basically like an inequality or whatever you want. We'll talk about some different types in a second where you can specify a range of things they can type in here.
For example, if you want the credit limit to be less than, let's say, $10,000, you put in less than $10,000. I'll zoom in so you can see it better. Shift F2. Less than $10,000. Or you can do less than or equal to, or you can do greater than, or you can do equal to, but that's just one value allowed. We'll say less than $10,000. I'll hit OK.
Now, the validation text is simply the message that you'll display to the user if that rule is violated. Otherwise, Access gives you this really basic system message. We're going to show you. Let's save that, first of all.
It says the data integrity rules have changed. Existing data may not be valid for the new rules. That's OK. This process can take a long time if you have a lot of records in your data already. I'll just say yes, go ahead.
The data I have in here is fine. If I had a record in there that already had a credit limit of $500,000 in it, then Access would warn you and say existing data doesn't meet your new rules. It won't prevent you, but you can.
If I go in here to, say, myself and type in 200--no problem. If I type in 50,000, I get that: one or more values prohibited by the validation rule. That's not a very user-friendly message, but at least it tells you that you can't do it. You can't save that value unless you hit escape or change it.
That's where the validation text comes in. That's so you can give the user a nice friendly message that says you have to type in the value like this. Put in "Credit limit must be $10,000 or less." Save it, and then go back in here.
If you violate that rule again, you get a friendly message: credit limit must be less than $10,000.
Now, they could type in a value that still matches that, like negative 10, and that's equally problematic. Let me leave that one in there, design view.
There are two ways you could do this one. Let me zoom in for you. You can either say between 0 and 10,000. That's one way to do it. That includes the endpoints. Or you can say greater than or equal to 0 and less than or equal to 10,000. Both ways work just fine. OK. Save it.
All right, data rules have changed. Now it says some existing data violates the new setting. Do you want to keep it, revert, or stop testing? Those are your different options. I'm going to hit cancel, then go back to here, and it lets you do it.
Now, let's come over here and change this to 30. That's fine. Change to negative five--not allowed. You'd probably want to change your prompt to "right between 0 and 10,000." I think most people get it, though. Negative numbers aren't allowed.
That's a basic validation rule with a currency value. The same thing works for numbers like family size.
Text values are a little more difficult. Let's say you are dealing with the National Association of People Whose First Names Start With the Letter R. So your first name field has to begin with the letter R. We'll come in here, go to first name. To do this, we'll have to use something called a wild card.
If you want to understand how wild cards work, you have to know the "like" keyword. I have a whole video that covers this. Go watch this. I'll put a link to it below the video in the description. You have to click on the "show more" and you'll see it.
But I can come in here, go to the validation rule, and type in (I'll zoom in for you, Shift F2) "like" and then, in quotes, "R*". That says it has to begin with the letter R and have any number of characters after it.
There are all kinds of things you can do with the "like" keyword and wild cards. I'm not going to cover them all in this video. Go watch that other video. Hit OK. Save this, say yes, and then cancel. It's going to test, and then we want to cancel the testing, but still allow it.
Go into here now. The existing data doesn't get changed, but if I type in something like Dave, I can't do it. But if I type in the word Rose, it works. First letter is R. It's not case sensitive.
That's a basic text validation rule.
Another handy type of validation rule is for date values. Let's go to design view. Let me get rid of that first name validation. Let's take that out of there. Go down to "customer since," find that field.
Validation rule--let's say you want it to be a value before October 23rd of this year. I'm going to zoom in for you so you can see it again. We're going to go "less than," and then inside pound symbols, because dates have to be in pound symbols, 2022-10-23. That's my birthday. I use the ISO date standard format on my computer, which is year-month-day. Want to learn more about that? I have a whole video on it. Go watch that one.
Hit OK, hit tab. That's the validation rule. It has to be before that date. Save it. Yes, cancel. Now go into your table, find your date. If I put in 1/1, it defaults to 1/1 of this year. That's fine. If I put in 2024-1-1, it says you can't do it, because it's after that future date.
Static dates aren't very friendly. So you might want to put something in here that's relative to the current date. We can use the date function for that. Replace that with "date". Here, I'll zoom in so you can see it. "Less than date." This puts today's date in there. That's a function, hence the open and close parentheses. You don't need that because "date" is what's called a reserved word. Access usually knows what you mean by just "date", but I'm an old-school programmer. I like to make sure I get the parentheses on there. Save it. Yes, cancel. Abort those tests.
Now come over here. It's going to be based on today's date. Today is July 25th, 2022. If I type in 6/1, there you go. The nice thing about the ISO date standard is you can type in a month/day, and it still defaults to the current year, so it still works. But if I type in 11/1, can't do it. That's a future date. So that one doesn't work.
If you want this to be something like 30 days ago, come in here and put in "date - 30". In Access, a unit of 1 is 1 day. So "date - 30" is 30 days ago. "Date + 30" is 30 days in the future. I have a whole video on that one too. "Date math" teaches you how all that works with fractions of a day, hours and minutes, and so on.
So, I'll say "date - 30" for 30 days ago. That would mean that I can't put dates in there that are before 30 days ago. If I come in here and put in today's date, which is control semicolon if you want to put today's date in there automatically, that works in Access and Excel. Not in Google Sheets. I've been using Google Sheets a lot lately, and they don't have that keyboard shortcut. Add that, Google. That has to be less than 30 days ago. If I put in 1/3, it works just fine.
If you want to learn more about validation rules and a whole ton of other stuff, I cover them in my Access Beginner Level 3 class. I'm actually in the process right now of re-recording this. For now, the one that's up there is a little older, but it's still all the same stuff. Access hasn't changed much since 2007. You will get a free copy of the new version. I'm re-releasing it very shortly. I'm re-recording it right now. Level one is free, level two is a dollar, level three is like six bucks. It's not much, and it's a whole bunch of real, helpful material.
There you go. There is your Fast Tips video for today. I hope you learned something, and we'll see you next time.
How do you become a member? Click on the join button below the video. After you click the join button, you'll see a list of all the different membership levels that are available, each with its own special perks.
Silver members and up get access to all of my extended cut TechHelp videos, one free beginner class each month, and more.
Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my code vault, where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free expert class each month after you finish the beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you finish the expert classes. These are the full length courses found on my website, not just for Access. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a diamond sponsor and have your name or company name listed on a sponsors page that will be shown in each video as long as you're a sponsor. You'll get a shout out in the video and a link to your website or product in the text below the video and on my website.
Don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free.Quiz Q1. What is the primary purpose of a validation rule in Microsoft Access?
A. To automatically back up database tables
B. To set limits on what data can be entered in a field
C. To import data from external sources
D. To format reports for printing
Q2. Where can you set a validation rule in Microsoft Access?
A. Only in queries
B. Only in tables
C. In tables or forms
D. Only in reports
Q3. What happens if data already in a table does not meet a newly set validation rule?
A. Access automatically deletes the invalid data
B. Access always prevents saving changes
C. Access warns you, but you may choose to keep the data
D. Access automatically corrects the values
Q4. Which of the following is a correct validation rule for allowing only values less than $10,000 in a field?
A. >10000
B. <=5000
C. <10000
D. >=10000
Q5. What is the purpose of the 'Validation Text' property?
A. To store the value that triggers a rule violation
B. To display a custom message when the validation rule is violated
C. To change the field's data type
D. To encrypt the data
Q6. How would you prevent negative numbers from being entered in a currency field, allowing only values between 0 and 10,000?
A. Set the validation rule to "<0"
B. Set the validation rule to ">0"
C. Set the validation rule to "between 0 and 10000"
D. Set the validation rule to "=0"
Q7. What keyword and wildcard combination would you use to require that a text field value starts with the letter R?
A. like "*R"
B. like "R*"
C. between "A" and "R"
D. equal to "R"
Q8. When applying a validation rule to a date field to allow only dates before a specific date, which format should you use in Access?
A. 'YYYY/MM/DD' in quotation marks
B. #YYYY-MM-DD# between pound symbols
C. DD/MM/YYYY without symbols
D. MM-DD-YYYY in brackets
Q9. What will the validation rule "<Date()" accomplish in a date field?
A. Only allows today's date
B. Only allows dates in the future
C. Only allows dates before today
D. Only allows blank dates
Q10. If you want to restrict a date field to dates within the last 30 days, what validation rule should you use?
A. >Date()+30
B. <Date()-30
C. >Date()-30
D. =Date()-30
Q11. What shortcut can you use to enter today's date in Access and Excel?
A. Ctrl+Enter
B. Ctrl+Alt+T
C. Ctrl+Semicolon
D. Alt+Shift+D
Q12. Which of the following describes how Access handles existing data when a new validation rule is added that the current data violates?
A. Deletes all violating records
B. Prevents saving the new rule
C. Provides options to keep, revert, or cancel validation testing
D. Silently ignores the issue
Q13. What is the main difference between using 'between 0 and 10000' and '>=0 and <=10000' as a validation rule?
A. 'between' excludes the endpoints
B. 'between' includes the endpoints, just like the other option
C. Only '>=0 and <=10000' works for numbers
D. Only 'between' works for text fields
Q14. What is a recommended way to provide a user-friendly error message when a validation rule is violated?
A. Enable database encryption
B. Use the Validation Text property to set a custom message
C. Rename the field
D. Set the format property
Answers: 1-B; 2-C; 3-C; 4-C; 5-B; 6-C; 7-B; 8-B; 9-C; 10-C; 11-C; 12-C; 13-B; 14-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone covers the topic of validation rules in Microsoft Access and how you can use them to enforce limits on data entry.
Imagine you have a customer database with a field for credit limit. By default, nothing stops someone from entering an extreme value, like five million dollars, as a customer's limit. While I might trust myself with that kind of credit, many businesses need to set restrictions to keep their data sensible and controlled. Fortunately, you do not need to dive into complex programming to manage this type of validation; Access has a straightforward mechanism built into tables and forms called validation rules.
To set up a validation rule, you would first open your customer table in design view. Most fields provide properties for validation rule and validation text. The validation rule is basically an expression, such as an inequality, that determines what type of data is allowed for that field. For example, if you want to set the credit limit so it cannot exceed ten thousand dollars, you use an expression that specifies "less than or equal to ten thousand."
There are a few different ways to write these expressions. You can allow values "less than" a certain threshold, or "between" a specific range. For instance, to keep credit limits at or below ten thousand and above zero, you can write a rule that sets the limits between zero and ten thousand. This prevents both oversized values and problematic negative numbers.
Whenever someone tries to enter data that violates your validation rule, Access displays a warning. By default, the warning is a generic error. This is where validation text comes in. You can craft a more friendly and instructive message, such as "Credit limit must be 10,000 or less." This gives users clear feedback and helps maintain good data quality.
Validation rules aren't just for currency or numeric values. You can set them up for text fields as well. For example, suppose you have a special reason to require all first names to begin with a particular letter, such as R. You can use a wildcard expression with the "like" keyword to enforce this, allowing only names that start with R followed by any number of characters.
Date fields can benefit from validation rules, too. Perhaps you want to ensure that a "customer since" date is not in the future. You can write a rule that the date must be "less than" today. You can even use Access's built-in date function for dynamic validation, so the rule always considers the current date when evaluating entries. For more specialized requirements, like only allowing dates from the past thirty days, you can adjust the rule accordingly by subtracting days from the current date.
These techniques help you enforce data integrity throughout your database without writing any VBA code. If you want to learn more about validation rules, along with many other essential Access features, I discuss them in detail in my Access Beginner Level 3 class. A new version of this course is in the works, but the current material is still highly relevant. Plus, when the updated version is out, anyone who purchased Level 3 will receive the new edition at no additional charge.
I also offer various membership levels on my website. Silver members and higher can view extended versions of TechHelp videos and get a free class each month. Gold members get access to all the downloadable databases and my code vault, with a higher priority for support questions. Platinum members receive all previous benefits, even greater support priority, access to all of my beginner-level courses across different subjects, and a free developer class each month after finishing the expert level.
My free TechHelp videos will always remain available, and I look forward to continuing to make them as long as you keep watching and asking great questions.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Using validation rules to set data entry limits in Access
Setting validation rules for currency fields
Defining numeric range validation rules
Customizing validation text for user-friendly error messages
Applying the Between operator for range validation
Using comparison operators in validation rules
Validation rules with wildcard patterns for text fields
Implementing the Like keyword for text validation
Setting validation rules for date fields
Using static dates in date validation rules
Applying the Date function in validation rules
Performing date math in validation rules
Keyboard shortcuts for entering today's date in Access |
