Use Conditional Formatting on a text box with an IIF Control Source

[TammyT]

I want to apply some text formatting on unbound text boxes, but it's not working.

I think I've narrowed down the problem - has something to do with the control source being an IIF statement. I say this because the conditional formatting works fine on other unbound text boxes on the report, whose control sources are just simple math.

Here is the control source info:

=IIf([txtP1_I5_1NA]=[fldCountReviews],"NA",[txtP1_I5_1Y]/[txtP1_I5_1Countable])

That works fine. Also: the result is formatted as a percent, 2 decimal points, & all of the fields like this one on the report are italic.

However, when I try, either with the contional formatting options on the report or with VBS, to tell it to turn any answer that is <= 60 red/bold, it generally just turns them all red/bold.

I have even tried using the additional formatting of if it's > 60 leave it black - that doesn't work.

Ideas?

Thanks!

 

[dhookom]

Your expression:
=IIf([txtP1_I5_1NA]=[fldCountReviews],"NA",[txtP1_I5_1Y]/[txtP1_I5_1Countable])
might return text or it might return a number. This is not good practice. I would use
=IIf([txtP1_I5_1NA]=[fldCountReviews],Null,[txtP1_I5_1Y]/[txtP1_I5_1Countable])
You can display "NA" by setting the Format property. Keeping the control value as numeric, you will have better luck with conditional formatting.



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TammyT]

Ah ha!! See - I knew that IIF statement had something to do with it!

Thanks - I'll try it in a bit. I was hoping you'd be online - you always give me excellent advise!

+Tammy

 

[TammyT]

Okay - take 2!!

I tried just changing the first text box from "NA" to Null, & I put in the code - didn't work; tried the conditional formatting box - turned it red/bold, even though the result was 92.00%!

Code I'm using is:

If Me.txtP1_I5_1Compliance <=60 Then
Me.txtP1_I5_1Compliance.ForeColor = vbRed
Me.txtP1_I5_1Compliance.FontBold = True
End If

???

(I didn't even get to the format part to display Null as NA!)

 

[dhookom]

IIf() may return a variant string. You might need to wrap the expression in =Val( IIf(...)). Or, you can do this in your code.

Check Help on the Format property for Numeric values to see how you can specify four different formats for a single numeric text box.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TheAceMan1]

How are ya TammyT . . .

Although your code is a form of conditional formatting, I thought you were talking about MsA's conditional formatting. Is any case correcting the code looks like this:

[blue]   Dim ctl As Control
   
   Set ctl = Me.txtP1_I5_1Compliance
   
   If ctl <= [purple][b]0.6[/b][/purple] Then
      ctl.ForeColor = vbRed
      ctl.FontBold = True
   Else
      ctl.ForeColor = vbBlack
      ctl.FontBold = False
   End If
   
   Set ctl = Nothing[/blue]

Last I remember [blue]60% = 0.6[/blue]

If you use MsA conditional formatting should Be:

[blue]   [txtP1_I5_1Compliance] < 0.6[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[TammyT]

Okay - just call me "DUH!!!" That's all it was, AceMan1 - I just needed to change 60 to .6!!

Okay - Now I can copy/paste that code into the other 6 reports that have the exact same info, just the "based on" is different! (has to do with unit vs worker vs agency, etc - don't ask!).

And then I can use it for the other 8 reports, that each have 6 related reports, as well! Just change the names of the controls.

Will I be up all night??? !!!!

But at least the code works - thanks to ALL of you!!!!