Conditional Formatting on a report 1

[newguy86]

Hi All,
I have a report setup that's based off of a query and in this report I have a Variance field that gets calculated on the fly based off of a couple of other fields in the report.

What I want to do is have the Variance field turn yellow if the value in that field is >15% of the value of the Total field that's included on the report.

The problem that I'm having is that the conditional formatting isn't working. In all honesty I don't think it even recognizes the Variance field as a number because I can't get any variable to work. I have the field in the report setup as a Fixed format in order to limit the number of places past the decimal and the calculation that's done in the Control Source is working without an issue but for whatever reason I cannot get the Conditional Formatting to generate when it's supposed to.

Any thoughts?

Travis

http://www.apexsystemsinc.com

 

[dhookom]

Any chance you could provide significant expressions you are using anywhere?

When you attempted conditional formatting, did you use Expression Is?

Although conditional formatting is usually the best solution, you can often use code in the On Format event of a section to change control properties.

Duane
Hook'D on Access
MS Access MVP

 

[newguy86]

I used both Expression Is and Field Value Is to try and get this to work.

I'm not exactly sure what you mean by "significant expressions" but I don't think I'm using any. The conditions that I have tried to use are as follows:

Field Value Is...greater than...[Forecast]*0.15

Expression Is...[Variance]>[Forecast]*0.15

I do know about using code in the On Format event but I figured that this was a simple enough request that the Conditional Formatting functionality should work and I wanted to make sure I wasn't missing something before resorting to that.

Travis

http://www.apexsystemsinc.com

 

[dhookom]

Have you tried replacing either Variance or Forecast with a hard-coded number of testing purposes?

Apparently Variance is the control/field in the section. Where is Forecast located? What is its control source?

Duane
Hook'D on Access
MS Access MVP

 

[newguy86]

Yes. I tried that and still recieved no response which is what lead me to believe that it wasn't recognizing the value as a number.

Forecast is stored in a table and gets pulled in through the query that feeds this report. Variance is only stored on the form and it's value gets generated when the report is run.

Travis

http://www.apexsystemsinc.com

 

[dhookom]

Variance is only stored on the form

No values get stored in a form. A control on a form might display a value but data is typically stored in tables.

Can you share how variance is calculated?

Have you tried wrapping Variance in Val([Variance])?

Duane
Hook'D on Access
MS Access MVP

 

[newguy86]

Variance Calculation:

=[Forecast]-[Actuals]

I just tried "Val()" and I still can't get any expression to work. Even if I try to set it up for a value greater than 0.

Travis

http://www.apexsystemsinc.com

 

[dhookom]

Again: Can you share how variance is calculated?

What do you see if you use the expression as a control source of a text box? Ideally you should see either 0 or -1.

Control Source: =[Variance]>[Forecast]*0.15

Duane
Hook'D on Access
MS Access MVP

 

[newguy86]

Forecast and Actuals are stored values in a table. The Variance calculation is simply the value of Forecast subtracted from the value of Actuals.

When I use the expression as a control source I do see 0 & -1 returning depending on the record it's referring to.

Travis

http://www.apexsystemsinc.com

 

[dhookom]

I would then mess around with some other formats in conditional formatting.

Duane
Hook'D on Access
MS Access MVP

 

[newguy86]

Apparently I am an id10t!

This whole time the issue was that the Back Style for text boxes in an Access Report automatically defaults to Transparent and I didn't realize that until just now. Once I changed the setting to Normal and tired the conditional format again it worked perfectly fine.

Travis

http://www.apexsystemsinc.com