470,593 Members | 2,352 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,593 developers. It's quick & easy.

Need help setting a validation rule. Microsoft Access

I need help setting a validation rule to accept the values 2021 or 2022 or 2023 (in that order) only.
4 Weeks Ago #1
2 9454
isladogs
352 Expert Mod 256MB
Easy enough to set the validation rule: Between 2021 And 2023
However, I'm unclear what you mean by 'in that order'
The rule just checks if the entered value is valid or not.

If you really want to show a range of valid entries, you could setup a table level lookup field using a combo with a value list for those years in that order. However, table level lookups are definitely not something I would ever recommend.
4 Weeks Ago #2
zmbd
5,438 Expert Mod 4TB
If you really want to show a range of valid entries, you could setup a table level lookup field using a combo with a value list for those years in that order. However, table level lookups are definitely not something I would ever recommend.
Ideally, your users should NEVER work directly with your tables, all data entry should be done thru the user-forms. With that said, I really wouldn't do a table level lookup combo box - can lead to some real issues down the line.

Table level validation is a completely different beast and is something I do quite regularly, using a limit such as isladogs has suggested; provided, that one is sure that there may not be a need in the future to change the table level validation. Would you need to add "2024" at some point?

So the solution Either do this in the query or in the user form.

I've attached a very simple database to illustrate the point.
Normally I would select one of the forms to open on startup; however, I wanted you to be able to see each of the options.
I've put the numbers in the queries and the tbl_years in the order given, however, you can reorder these as needed with with a sort one the tbl_year in the row source or by changing the order in the static list... etc... most flexible either way.
Attached Files
File Type: zip _SimpleDatabase.zip (22.0 KB, 10 views)
4 Weeks Ago #3

Post your reply

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

Similar topics

6 posts views Thread by Chuck | last post: by
17 posts views Thread by Pam Ammond | last post: by
5 posts views Thread by michael.meenan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.