Conditional formatting problem 1

[Chris1977]

This is probably simple but i'm stumped.

There is a field on a report which results in a value.......which would be 21.76 or 9.76 etc. The format we specified was if that field value is greater than 20 then the field would be grayed out while the others remain in the normal format. This is a calculated field which is the result of adding two other fields on the same report. Right now those greater that 20 are being grayed out however those under 9.99 are also being grayed out.....which makes no real sense. The number in that field is a fixed diget with 2 decimal places specified. So in short how do i get the numbers under 10 to not be subject to the same formatting as those over 20?

Any help would be appreciated.

Thanks

 

[PaulBricker]

What code are you currently using to "Gray Out" the value.

Paul

 

[Chris1977]

The formatting of the field is not being controled through code but rather the options in "Conditional Formating" under the "Format" menu. We just have it placing a grey shade on the field.

It appears that it can't recognize single digit numbers. Is there a way to have it display as 09.99 for example?

 

[PaulBricker]

You could post what you have in the Conditon1 statement. In the mean time, you could do it directly in the Report. You would use the Format Event for the section the information is in, usually the Detail section, and the code would look like this.

If Me.FieldName >=10 Then
Me.FieldName.BackColor = vbGrey
Else
Me.FieldName.BackColor = vbWhite
End If

I'm not 100% clear what you mean by Grayed Out but this will get you close. Then you can post back with specific problems.

Paul

 

[Chris1977]

we tried doing this through VB but encountered type mismatch errors.

All we want it to do is change the formatting of a field on a report based on its value. We've accomplished this with other fileds but there is one in particular that is not cooperating.

To do this with other fields we opened the report in desgin mode then slected the filed and clicked on "format" then "conditional format from the menu bar. we chose a grey background as the format condition but could just as easily have it display in bold or red etc.

The problem is that Access thinks anything below 10 is actually greater than 20 and thus formats it using the format we selected. We think it is because numbers below 10 appear as a single disgit but that is just a theory.

Is there an issue with Access or is it somehting simple we are missing?

Thanks.

 

[PaulBricker]

You field must be Text. 9 will be interpreted a greater than 20 in a Text field. In the Conditional Formatting box try this.
Change the FieldValueIs line to ExpressionIs then in the next box put
Val(FieldName)>=10
then select the background you want if this expression is true(which is the gray).

Paul

 

[Chris1977]

Hey Paul,

I did that to no avail. The fields on the report look no different as the formatting was not applied. The syntax i used was:

Val (txtAHT) > 20 which translates to Val("txtaht&quot>20

Any further ideas?

Thanks again

 

[PaulBricker]

I've never used that Conditional formatting. it may be that you can't make the change there. Can you do it in the Format event for the Report

If Val(FieldName) >=10 Then
Me.FieldName.BackColor = vbGrey
Else
Me.FieldName.BackColor = vbWhite
End If

See if that does it.

Paul