Conditional Formatting Displaying Incorrectly 1

[awl]

In the “Report Footer” I have 3 Text Boxes: A/B = C (Text Box C Displays Percentages). My goal is to have green for (90-100%), purple (70-89%), and red (Less than 70%). The following Conditional Formats are set for Text Box C.

Condition 1: Field Value is: greater than or equal to 0.9, set for green
Condition 2: Field Value is: between 0.7 and 0.899, set for purple
Condition 3: Field Value is: less than 0.7, set for red.

I have no problem with the returned values; I wanted the 2 decimal places to be shown in the values; that is what I get. The problem is when the values in Text Box C change, the color is always red; ie: 31/24 = 77.42% or even if the value = 100.00%, the color is red. I have tried the whole numbers instead of decimal numbers for above 1) 90, 2) 70-89, 3)70; with no luck. The Name, Control Source and Format is provided for each of the 3 text boxes:

Text Box A: Name: Summary REQ
Control Source: =Sum([Required])
Format: 0; 0

Text Box B: Name: Summary Comp(REQ)
Control Source: =Sum(IIf([Required]=[Completed] And [Optional]>=[Completed],"1","0&quot)
Format: 0; 0

Text Box C: Name: Percentage
Control Source: =(Sum(IIf([Required]=[Completed] And [Optional]>=[Completed],"1","0&quot)/(Sum([Required])))*1
Format: 0; 0.00%

Any help would be helpful. Thank you. Curtis.

 

[dhookom]

Without reviewing the total logic of this, one issue is using text/strings rather than numeric. I would try expressions like:
Control Source: =Sum(IIf([Required]=[Completed] And [Optional]>=[Completed],1,0))

You make a lot of extra work attempting to sum strings.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[awl]

I deleted the quotes around the numbers and I still have the same problem...... curtis.....

 

[dhookom]

Is text box C displayed left aligned? Are the values of C displaying as expected? You might want to try:
=Val(Sum(IIf([Required]=[Completed] And [Optional]>=[Completed],1,0))/(Sum([Required])))


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[awl]

The values displayed in the Text Box C is centered. Text Box C displays the value as expected: when Text Box A = 31, Text Box B = 24, Text Box C results in 77.42% as I want it to; the 77.42 value with the % sign is displayed. It is just that the color remains red, and not green as I want it to. I did change my Control Source to exactly as you have written, and the value still remains 77.42%, in red. Curtis....

 

[dhookom]

I blieve your problem is that you have negative numbers which are ALL less than .7. Your format property for C would should positive numbers with no decimal places and negative numbers with two decimal places but no indication that the numbers are negative.

If you change the format to 0.00%;( 0.00%), you should see your problem.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[awl]

Yes, thank you, it works well now. I changed the format as suggested, but also added to the control source "*-1" to get the results I needed, and changed the Decimal to 0, where it was "auto" previously. The 3 items are as follows for Text Box C: Control Source: =Val(Sum(IIf([Required]=[Completed] And [Optional]>=[Completed],1,0))/(Sum([Required])))*-1 Format: 0.00%;(0.00%) Decimal Places: 0

Thank you for your help..... Curtis....