Conditional Formatting + Unbound control (1 Viewer)

jeran042

Registered User.
Local time
Today, 14:03
Joined
Jun 26, 2017
Messages
127
I need conditional formatting to take 3 arguments:
1 - Greater than or equal to 5 - form control txt_Percent
2 - Less than or equal to -5 - form control - txt_NPercent
3 - Greater than 5000 - form control - txt_Dollar

Here is what I have so far:

Code:
([txt_PC_Due]>=[txt_Percent] Or [txt_PC_Due]>=-[txt_NPercent]) And [txt_Var_Due]>=[txt_Dollar]

Im sure there is a better way to do this, and any suggestions will help,
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:03
Joined
Oct 29, 2018
Messages
21,358
Hi. If you're trying to have three separate formatting, then you'll need three separate conditions. I'm having a hard time deciphering the rules you posted above. Can you maybe post a screenshot, in case it helps clarify what you mean? Thanks.
 

jeran042

Registered User.
Local time
Today, 14:03
Joined
Jun 26, 2017
Messages
127
3 separate rules may be the way to go.

Here is the screen shot anyway. Along the bottom middle the box with the "4" is the txt_Percent, the box with the "-7" is the txt_NPercent and the box with the "5000" is the txt_Dollar.
 

Attachments

  • CF_060619.jpg
    CF_060619.jpg
    104 KB · Views: 55

theDBguy

I’m here to help
Staff member
Local time
Today, 14:03
Joined
Oct 29, 2018
Messages
21,358
3 separate rules may be the way to go.

Here is the screen shot anyway. Along the bottom middle the box with the "4" is the txt_Percent, the box with the "-7" is the txt_NPercent and the box with the "5000" is the txt_Dollar.
Right, if you want a control to have three different background, for example, red for below, green for good, and yellow for over, then I would use three conditions. You just have to make sure you stack the conditions in the correct order because the first True condition (I think) gets applied.
 

jeran042

Registered User.
Local time
Today, 14:03
Joined
Jun 26, 2017
Messages
127
Thank you,

I actually dont need three conditions. Is just 2, greater than 5 OR less than -5 AND over 5000. And if those two conditions are met, just light up, no scale needed.

Does that change how you look at the question?

Thank you again for your time
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:03
Joined
Oct 29, 2018
Messages
21,358
Thank you,

I actually dont need three conditions. Is just 2, greater than 5 OR less than -5 AND over 5000. And if those two conditions are met, just light up, no scale needed.

Does that change how you look at the question?

Thank you again for your time
Depends, if you're saying if either condition is met, just highlight with one color (yellow, for example), otherwise, don't do anything, then no, you only need one condition.
 

jeran042

Registered User.
Local time
Today, 14:03
Joined
Jun 26, 2017
Messages
127
I might be misunderstanding what your saying.
2 conditions have to be met.
  1. Greater than 5 OR less than -5
  2. AND greater than 5000
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:03
Joined
Oct 29, 2018
Messages
21,358
I might be misunderstanding what your saying.
2 conditions have to be met.
  1. Greater than 5 OR less than -5
  2. AND greater than 5000
Probably, I was referring to a "rule" and using the term condition. So, what happens if you tried a similar test as we did in your other thread? Something like:
Code:
(Something > 5 OR Something < -5) AND (Something > 5000)
Remember those parens placements?
 

jeran042

Registered User.
Local time
Today, 14:03
Joined
Jun 26, 2017
Messages
127
I doesn't work as expected, for a value of -35, no formatting appears. However for any positive values it seems to work as expected. I.E. greater than 5 and greater than 5000, the formatting appears.

This is a continuation of my previous thread. I didn't state the problem correctly in the previous thread. I don't think.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:03
Joined
Oct 29, 2018
Messages
21,358
I doesn't work as expected, for a value of -35, no formatting appears. However for any positive values it seems to work as expected. I.E. greater than 5 and greater than 5000, the formatting appears.

This is a continuation of my previous thread. I didn't state the problem correctly in the previous thread. I don't think.
Is this how you have it in your condition?
Code:
Expression Is > ([Text1]>5 OR [Text1]<-5) AND ([Text2]>5000)
or maybe something like this?
Code:
Expression Is > ([Text1]>[txt_Percent] OR [Text2]<[txt_NPercent]) AND ([Text3]>[txtDollar])
 

jeran042

Registered User.
Local time
Today, 14:03
Joined
Jun 26, 2017
Messages
127
Ahhh, you know what??

One of the conditions (obvously) isnt being met. Im saying greater than 5000, but I also have to stipulate less than -5000.

So here is what I am ultimately trying to do. On a budget report, I need to identify line items that are more than 5% (positive or negative) and have a variance of more than $5k (again positive or negative). I was missing a key piece by not accounting for the less than -$5k.

Based on that, I would think that this world work, but it still doesnt:

Code:
([txt_PC_Ag_In]>=[txt_Percent] Or [txt_PC_Ag_In]<=[txt_NPercent]) And ([txt_Var_Ag_In]>=[txt_Dollar] Or [txt_Var_Ag_In]<=[txt_NDollar])

I think I'm making this way harder than it has to be
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:03
Joined
Oct 29, 2018
Messages
21,358
Ahhh, you know what??

One of the conditions (obvously) isnt being met. Im saying greater than 5000, but I also have to stipulate less than -5000.

So here is what I am ultimately trying to do. On a budget report, I need to identify line items that are more than 5% (positive or negative) and have a variance of more than $5k (again positive or negative). I was missing a key piece by not accounting for the less than -$5k.

Based on that, I would think that this world work, but it still doesnt:

Code:
([txt_PC_Ag_In]>=[txt_Percent] Or [txt_PC_Ag_In]<=[txt_NPercent]) And ([txt_Var_Ag_In]>=[txt_Dollar] Or [txt_Var_Ag_In]<=[txt_NDollar])
I think I'm making this way harder than it has to be
Hi. Unfortunately, it is hard for us to say if the syntax is correct or not because we can't "touch" the actual database to make sure we're using the correct names on the form or table. How about adding a calculated column in your query to clearly show a Yes or No if the condition is met? You can then use this new column for your conditional format.
 

jeran042

Registered User.
Local time
Today, 14:03
Joined
Jun 26, 2017
Messages
127
I'm confident on the names, I suppose I'm not confident on the syntax.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:03
Joined
Oct 29, 2018
Messages
21,358
I'm confident on the names, I suppose I'm not confident on the syntax.

The syntax looks fine from here. Have you tried putting it in the query? What did you get?
 

jeran042

Registered User.
Local time
Today, 14:03
Joined
Jun 26, 2017
Messages
127
The problem is that the text boxes are unbound, and are not pointing back to a field in a query. They are calculations.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:03
Joined
Oct 29, 2018
Messages
21,358
The problem is that the text boxes are unbound, and are not pointing back to a field in a query. They are calculations.

So, are you not able to post a sample file? The next steps I am thinking of suggesting will take a long time to explain.
 

Users who are viewing this thread

Top Bottom