# values above 1000 do not change to red

This topic is 6440 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

• Newbies

I am using the following equation to change background colors:

Case (Inc Hrs < 150;GetRepetition ( gColors; 1);

Inc Hrs >151;GetRepetition ( gColors; 2);

Inc Hrs >301;GetRepetition ( gColors; 3))

1=green,2=yellow,3=red

I've run various figures in the fields to change the value that is used to calculate which repetition to get, seems to work find until it hits a value of 1000 and above, color changes to yellow. Any suggestions.

##### Share on other sites

Case() stops at the first true value, you need to reorganize your tests like this:

Case (Inc Hrs < 150;GetRepetition ( gColors; 1);

Inc Hrs >301;GetRepetition ( gColors; 3);

Inc Hrs >151;GetRepetition ( gColors; 2))

because if its greater than 301, it will definitely be greater than 151 also.

Another possibility to consider, if you like "small" like I do, and hate typing half as much as me:

GetRepetition( gColors; Case( Inc Hrs < 150; 1; Inc Hrs > 301; 3; Inc Hrs > 151; 2 ))

##### Share on other sites

What if Inc Hrs is 302? The 2nd repetition of gColors is retrieved and the Case terminates because Inc Hrs > 151. You'll never reach the third test in the Case. Try working backwards. Case( Length(Inc Hrs); Case( Inc Hrs > 301; GetRepetition( gColors; 3 ); Inc Hrs > 151; GetRepetition( gColors; 2 ); gColors ) ).

The Length simply ensures that Inc Hrs isn't empty before a value is returned. The final option is gColors because GetRepetition( gColors; 1 ) is redundant. The first repetition of a field is always returned, unless you specify an alternate one or a different repetition is being set.

##### Share on other sites

I just noticed that from 150 - 151 you've not requested a result, because 150 is neither less than 150 or greater than 151. Is this really what you want?

##### Share on other sites

If you want to get tricky and remove the Case altogether,

GetRepetition( gColors; (Inc Hrs < 150) + 2 * (Inc Hrs > 151) + (Inc Hrs > 301) )

##### Share on other sites

• Newbies

Your solution worked great, in fact both worked and you are right I do like to keep the expression short. It never crossed my mind to swap positions of the last two repetitions. Thanks for the help.

##### Share on other sites

• Newbies

Looks like there are various flavors of this solution. Thanks for another solution.

##### Share on other sites

• Newbies

I started out using the less than/equal sign but kept getting an error message, elected the other route. The 1 represents one page for an hour estimation not really critical but I'll see with this new method if I can get it fixed and working correctly.

##### Share on other sites

Note that you will need to specify the table name for each field. Maybe this is why you were getting an error?

##### Share on other sites

This topic is 6440 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account