Validation rule for days available

[Jackson]

Not sure if I'm is the right place or not, if not let me know. I need a validation rule for at field where I can enter what days an employee is available. Example: M, T, W, Th, F, Sa, Su - I've tried the "Or" but it only lets me enter just one day. I need to l enter something like M, W, F, Sa all in the same field. I don't want anyone to be able to enter anything accept M, T, W, Th, F, Sa, Su

Completely new and struggling and help will be appreciated.


Jackson

[NTC]

well a rule is universal and you want it per employee - so you don't want a rule in terms of a table property or anything like that

you really need an Availability Table; 2 fields: Employee, Day

you don't want a side-by-side string separate by commas as it will not work in queries....

the way this info is used & displayed in terms of the user experience, the user interface and reports is very situational and so could involve sub forms or combo boxes or whatever....but you definitely do want to approach it with an Availability Table.

[Micron]

I think you're saying you want a multi value field in order to store more than one value in a field in a particular record, as well as have a validation rule - two issues.
Many experienced Access developers will tell you to avoid mvf's like the plague. They violate basic normalization rules, and frankly it amazes me that MS saw fit to create them in the first place, but to do so is your privilege.

As for the validation, I've no idea where you want to do this. In code? If so, use the form control AfterUpdate event.
In the validation rule property? ="M" Or "T" Or "W" (or whatever values you choose) and write a validation message in the Validation Text property.

[Micron]

Agree with the table approach (I neglected to address that in my haste).

you don't want a side-by-side string separate by commas as it will not work in queries....

It will if you write a function that parses the values and call the function from a query. However, this is somewhat advanced programming, and I wouldn't recommend it for a beginner, especially since it also requires code to concatenate the values in the first place. One could also use a csv list, but why complicate things when a table approach not only solves the issue, but follows the rules.

[Jackson]

Thanks to all, I'm taking an access course and they put all the multi-values in one field, not sure why because I just started the course. After looking at it I couldn't figure out how you run a query or search if I wanted to see all employees that can work on a given day. Maybe they are going to show how later down the line but the code would be beyond me. Thanks to all who answered. I'll leave this open for a little just in case someone want to add something.

Thanks once again.

[Micron]

Your comment about being in a course prompts me to add this quote from here (see table - What's Mixed (good and bad)
http://allenbrowne.com/Access2007.html (an excellent source of knowledge that you should bookmark)

The relational structure behind multi-valued fields is not accessible.
Developers will find it harder to handle complex data types. Any generic procedure you write must be capable of handling fields that contain fields. You cannot use a table with a multi-valued field in an IN clause (i.e. in another database.)

Another of their issues: https://support.microsoft.com/en-us/kb/926701

It's possible that some of their shortcomings could have been rectified by now, but my point would be this: given their past issues and the fact that people still post in this forum because they cannot get the information out of them in a normal manner, why would you use them? IMO, they present a shortcut for people who are either too lazy to properly structure their data model, or they simply don't know better (or both). If they are being taught in a classroom environment, then sure, take in whatever information is presented and don't put the instructor on the spot by presenting rebuttal evidence in front of the class. Just know that they are a shortcut to db creation and ask yourself if this was a project YOU were paying for, would you want the developer to take shortcuts as opposed to providing a properly normalized db structure?

[Jackson]

It's a beginner course so they may be using simple methods at this level. Thanks for the help, I'm sure I have a learning curve a mile long in my future.