Conditional formatting dependent on values from a different field

[Cavebear]

Hi

I have set up a series of calculated fields creating percentages input from their corresponding input fields: These need to act as traffic lights on an input form. And was intending to use conditional formatting for this.

The hard bit is the traffic light criteria vary depending on another field. So I cannot use the conditional formatting as standard.

Is there a way of doing it in code? I am way out of my depth now.

What I was thinking the logic might be something like:

If fld1 = "X" AND 'this field' <3 Then 'colour red'
ELSEIF fld1 = "X" AND 'this field' is between 1.5 and 3 then 'colour amber'
ELSEIF fld1 = "X" AND 'this field' is between 0 and 1.5 then 'colour green
ElSEIF fld1 = "Y"

And then going through the whole thing all over again until I have gone through all variants of fld1 - however I have no idea of the syntax for this!

Any help greatly appreciated!

 

[theDBguy]

Hi. I think whatever you can do with Conditional Formatting using VBA can be done using the UI as well. Conditional Formatting can/should be able to refer to other fields within the same record. For example, you could use something like: Expression Is: [ThisField]<>[ThatField] AND [ThisField] > 1 AND [ThatField] < 100

 

[Gasman]

In 2007 you can only have 3 conditions using the UI.?
Is that different for later versions?

 

[theDBguy]

In 2007 you can only have 3 conditions using the UI.?
Is that different for later versions?

I think starting with Access 2010, you can have up to 50 conditions now using the UI. However, you're still limited to only 3 using VBA.

 

[Gasman]

I think starting with Access 2010, you can have up to 50 conditions now using the UI. However, you're still limited to only 3 using VBA.

Wow, that is impressive

 

[theDBguy]

Wow, that is impressive

I agree. It would have been better if "they" remembered to update VBA when they updated the UI.