Field validation multiple choice

[rjstephan]

I have a field I want to validate to contain one or more of only five specific words, ie, red, green, blue, white, yellow. The IN expression requires ONE of a list, but I want to be able to have more than one from the list, but always only from the list. Example of Color Field in: Record 1 - blue; Record 2 - blue green; Record 3 - (cannot enter black). What is the proper /macro/expression/formula to achieve this?

 

[dynamictiger]

Answered in Table section

 

[rjstephan]

The answer given in the table section doesn't work.

 

[Junior1544]

in the validation rule put this. (I think.)

is "red" or "blue" or "whatever color" ...

see how that works??

that's the easiest way i can think of, if it doesn't work send me a note and i may be able to give another answer.

--Junior Junior1544@yahoo.com
Life is change. To deny change is to deny life.

 

[rjstephan]

Junior, that works if you only want one of the possilbe allowed words --- but I want to have one or more of the allowed words, but only the allowed words. Take a look at my example posted in the original question. Thanks

 

[dynamictiger]

You still don't get it do you.

You make two fields.

On your form you make one field only called something else.

On entering the data you split the entry to the two fields and validation runs.

 

[rjstephan]

I am not sure who it is that doesn't get it.

I don't know how you would split the single form entry to two separate fields in the table, but in any event, for that approach to work, I would have to have FIVE table fields in the event the entry has ALL FIVE PERMITTED WORDS.

Get it? Thanks.

 

[WildHare]

It sounds like you have a bit of a squicky here. If I read the question correctly, you have five possible colors, and the value for the field, if it is NOT null, can be from one to five of those colors, and can be multiples, e.g. "RED", "RED BLUE", "GREEN", "GREEN YELLOW" etc etc..or even "RED BLUE GREEN WHITE YELLOW" It's really an array that you're working with, and they can be a bit sticky.

There are two ways of doing this, neither of which are very pretty. You could make this "color set" guy a single-field child record of the item in question, as a "many" side of a one to many relationship, where there could be NONE or up to five child records, each with a color:

Main Guy
Key1, FieldA, FieldB, FieldC
    |
    |
Color Segment:
Key1, Color
Key1, Color2
... etc

The other way, is to CONTROL data input, such that your color is picked from a list box or a set of option buttons, and the 'text' of your choice is concatenated upon further selections:

ColorField = ListBoxSelection1
...{another selection made}
ColorField = Color Field & " " & ListBoxSelection2

You could probably handle this whereby the list box gets recalculated after a selection to remove the color just selected, so you can't accidentally do a "RED RED". I'd probably also do something to institute a "clear field" where you can set the value back to NULL if you want.

Post back if further clarification needed.








Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[rjstephan]

Jim,
You have given the best response so far. Could this perhaps be done with a macro with conditional statements and the set value action?

 

[WildHare]

I can't recommend macros, esp. for data validation. I'd prefer you to do it with a sub-table arrangement where there is the child segment of colors. I always think it best if the DATABASE handle data validation rather than a/the program. Just create a single-field table with your COLOR field, and a foreign key field back to your parent record as in my example, and set the input to this color field as a value list-combobox or list box of your five colors.

If you are extremely baffled, I might have the time later today to whip up a quick example.

Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com