Access Report Conditional Format based on If

[afs24]

I'm trying to conditional format in a report. Below is what I'm trying to do:

=IF([Work Function]="CARTPICK",[MaxOfCARTONS]/[SumOfHours],[MaxTRANS]/[SumOfHours]))

I'm sorting my report by the header Work Function. Why doesn't this work? thanks

 

[Andrew Fergus]

I haven't tested your formula but it looks like there are a couple of syntax problems. Try using IIF instead of IF and it looks like there is an extra bracket at the end of the formula. A couple of other things to check is that the field names are correct (you have MaxOfCartons and MaxTrans - are these names correct?) and the formats of the fields match what it is you are trying to do (the formats in the underlying query and table). BTW, you could calculate this field in the underlying query rather than the report and I would suggest you do that anyway.
HTH, Andrew.

 

[Norie]

Are you actually using conditional formatting? Or are you trying to calculate a value in depending on the value of a field?

Conditional formatting requires a TRUE/FALSE value.

Your IF will return a number which will probably be regarded as true.

 

[afs24]

I actually didn't do this correctly. I want to write an if statement in the conditional formating like the following example:

=IIF([Work Function]="CARTPICK",<50,0)

I have 4 work functions I need to format and include in this formatting:
PUTAWAY <25
LOADING <25
RECEIVING <20

I need my if statement to look for all four work functions? thanks

 

[Andrew Fergus]

Hi, the latest example is very different to your first and I'm confused as to what you are trying to achieve.

Are you wanting 4 conditional formatting tests, with 2 conditions for each? In plain English, test 1 is if the [Work Function] equals "CARTPICK" and if the value of something else is less than 50, then you want to apply a conditional format, using the Format -> Conditional Formatting in the report design? But if [Work Function] = "PUTAWAY" and something else is less than 25 then use another format? And so forth for test 3 and 4? Is this what you want?

Andrew

 

[afs24]

Yes, I have 4 diffenet conditions but only 1 test. Example

if CARTPICK <50 then I want it to turn yellow but if it's eqaul or greater then I want it to stay as is. It only lets you do 3 conditions but I have 4. I know I can write a formula in one conditions for all four but I'm not sure how. thanks

 

[SydneyGeek]

You can't do that easily with Conditional Formatting, but you can put some code in the Detail_Format event of the report. This link shows how -- http://www.mrexcel.com/board2/viewtopic.php?t=129541

Denis