Conditional Formatting Problem

[DanKoz]

I am having problems conditionally formatting the incorrect answers to a survey and could use some help.

There are 25 questions and 25 corresponding correct answers that can change over time. Some answers are currently “True” or “False”, some are multiple choice, and other have a range of responses from 1 to 5.



The acceptable answers to those questions that have a range of responses from 1 -5 can either be a 1 or a 2, or a 4 or a 5, depending on how the question is worded. The table that stores the correct answers allows for multiple values (e.g, “4,5” is stored in that cell.)

I designed a form that displays the correct answers based on the selected time period. “frmCorrectAnswers”

Here is the problem:

Question #9 (Q9) is a True or False and the answer (A9) is True. My conditional formatting rule says

Field Value is not equal to Forms![frmCorrectAnswers]![A9] then display the answer in red. This works fine, the answer turns red when it is incorrect.

Question #11 (Q11) has a range of responses from 1 to 5, with the acceptable answers being a 4 or a 5. The table with the correct answers, and hence also frmCorrectAnswers, shows a value of “4,5” for the answer (A11). (without the quotes).

If I hard code the conditional formatting rule as follows:
Expression is Eval ([Q11] Not In (4,5) ) then display the answer in red. This also works fine, the answer turns red when it is incorrect.

However, if I write the conditional formatting rule as follows:


Expression is Eval ([Q11] Not In (Forms![ frmCorrectAnswers]![A11] )) then display the answer in red. This DOES NOT work. I do not get any error messages but the text remains black even when the answer is incorrect.

The form is loaded and the reference to the answer in the form works just fine for question 9 above (Field Value is not equal to Forms![ frmCorrectAnswers]![A9] . And the Expression is Eval ([Q11] Not In (4,5) also works fine when the correct answers are hard coded. But when I try to combine the Expression is Eval ([Q11] option with a reference to thecorrect answer on the loaded form Not In (Forms![ frmCorrectAnswers]![A11], then it all falls apart. The color of the text for the answer does not change to red when it is the wrong answer.


Any suggestions would be greatly appreciated.

Thanks,

Dan

[June7]

Is field A11 a multi-value and were values entered by selecting from a multi-value combo or list box? If not, then 4,5 is just a single text value. If this is a multi-value field, it is possible field cannot be referenced in conditional formatting. I just tested conditional formatting with multi-value field and it does not work.

Review this article http://office.microsoft.com/en-us/ac...001233722.aspx
I refuse to use multi-value fields because of the issues they present.

[DanKoz]

Thank you!

That is exactly the problem. I changed the answer field from a Multiple Value field to a single value in which I entered "4,5" as the value (without the quotes) and it worked just fine. However, when it was a field that allowed multiple selections from the combo box it did not work, as you implied, even though the values in the field looked to same, i.e., "4,5".

Thanks, again.

Dan

[June7]

You have the In function working with the string value? Does not work for me. I had to use: Eval(InStr([A11],[Q11])=0)

[DanKoz]

Here is the conditional format code that I am using, and it is working:

Expression is Eval([Q11] Not In (Forms![frmCorrectAnswers]![A11])) then make the text red.

The value in the field Q11 is 1 (i.e, the answer provided by the student) the correct answer in field A11 is 4,5, entered as text and not a multi-value field per your suggestion. Since 1 is not in 4,5 the answer of 1 turns red.

Dan

[June7]

Interesting, cannot get that to work and I am also using Access2010. Well, if it does what you need...

However, this did lead to discovery 2010 no longer limits conditional formatting to 3 conditions. Nice!

[boblarson]

However, this did lead to discovery 2010 no longer limits conditional formatting to 3 conditions. Nice!

Yeah, you can have 50 conditions now.