Conditional formatting - what's wrong with me?

[Mal Ba]

My first introduction to this forum but I need a bit of help on conditional formatting.

I have a calculated field in a report: =IIf([2018-19D] Is Null,"0",[2018-19D]) & "+" & IIf([2018-19S] Is Null,"0",[2018-19S])
which, pretty obvously appears, on the form as something like this 0+0, or 1+0, or 0+2 ... you get the idea.

I want to colour the text box (the text box is called [Flag]) in green if the first entry (from the field is [2018-19D] is >0
If I use the following condition on the field [2018-19D] : Value>0 it works to colour that field green but
if I use the following expression on the field [Flag] : [2018-19D]>0 it does not colour the flag field green.

I've also tried the following expressions on the[Flag] field just in case: [2018-19D]<>"0", and CDbl([2018-19D])>0
and I've tried Left([Flag],1)<>"0" as well
but these have no effect either.

I'm obviously missing something, but what?

 

[Gasman]

You put the condition on the Flag control.?
Use the expression option and perhaps

[2018-19D]<>0

 

[theDBguy]

Hi. Welcome to AWF!

I agree. You apply the conditional format to the control you want to highlight, not the one you're checking.

 

[Mal Ba]

The condition is on the Flag control, the Expression option was what was being used, sorry if I was confusing.
Addendum: if I set up another unbound control called [Blank] and put [2018-19D]>0 into the expression for CF for that, it works
If I put [2018-19D]>0 into the expression for the [Flag] control which has a calculated field in it, as above, it doesn't.

 

[Mal Ba]

Hi. Welcome to AWF!

I agree. You apply the conditional format to the control you want to highlight, not the one you're checking.

I was: the conditional form is applied to the control I want to highlight [Flag] not the one I am checking. I am putting the Expression CF into the [Flag] control which I want to highlight. The Expression being used is: [2018-19D]>0. Regardless of the value of [2018-19D] the conditional format is not applied to the control.

 

[theDBguy]

I was: the conditional form is applied to the control I want to highlight [Flag] not the one I am checking. I am putting the Expression CF into the [Flag] control which I want to highlight. The Expression being used is: [2018-19D]>0. Regardless of the value of [2018-19D] the conditional format is not applied to the control.

Just for fun, try:

Expression Is: Val([2018-19D])>0

 

[Mal Ba]

Addendum: If I delete the formula: =IIf([2018-19D] Is Null,"0",[2018-19D]) & "+" & IIf([2018-19S] Is Null,"0",[2018-19S]) from the Control Source property of the control, the conditional formatting works. With the formula in the control, it does not. The work around I have used is to use a separate unbound control behind the [Flag] control which contains the CF, with the bound [Flag] control (with a transparent background) on top. This gives the effect required. I would love to know however why you can't put a CF in the bound field.

I have now hit an even odder problem (I don't remember Access being this buggy) which I will post separately, because it is leading me to wonder whether I (or anybody) should rely on Access at all - so I really would like an answer to that one, plese

 

[Gasman]

Perhaps Access cannot determine the value of that formula, but needs a hard coded value?
So put a hidden control on the form which refers to that control with the formula, and use that in the CF?

 

[Mal Ba]

Perhaps Access cannot determine the value of that formula, but needs a hard coded value?
So put a hidden control on the form which refers to that control with the formula, and use that in the CF?

That's what I have done. See the post directly before yours, where I said I'd done that as a work-around. Work arounds shouldn't be needed though especially when one doesn't know why: I guess it does think the formula is too complicated. Maybe something to do with the order things are evaluated on the form. Can't find a reference to that in any documentation though. Still we do have a work around.

 

[arnelgp]

IIf([2018-19D] Is Null,

I think "Is Null" only works in SQL statement.
if you want to test the field, use IsNull():
-
IIf(IsNull([2018-19D]), "0", ...

 

[Mal Ba]

No. Both work. I also tried both, just in case.

 

[bastanu]

Just wondering what happens if you try this formula instead:
=Nz([2018-19D])+Nz([2018-19S]) (which is equivalent to =Nz([2018-19D],0)+Nz([2018-19S],0))
with your original one I believe you end up with a string ("0") that you try to compare against a number (0).
Cheers,

 

[Mal Ba]

Nice idea and thanks, tried it and it makes no difference; still doesn't register make the CF work. I'd also tried comparing it with 0 and "0" as well as null but still it doesn't do anything. Putting the CF into a separate control seems to be (oddly) the only way.

 

[bastanu]

Strange as I have just tested a similar formula and seems to be working for me.
Cheers,
Vlad

 

[Mal Ba]

Definitely not working for me. It is odd, because I would have expected it to. I've now found another instance of a person on the web having the same problem with Access 2016 (though I am using Office 365) and an example on https://www.accessforums.net/showthread.php?t=72751 . Neither seemed to be resolved and other people did not seem to be able to replicate the issue every time. It looks like a bug. Typical though that the first time I try to do something in Access for about 5 years, I hit what looks like a bug. That just about sums life up. As I say, I have a work-around of putting the CF in a different control to that containing the formula for the RecordSource and overlaying the two. So I think I am going to have to go with that. Thanks.