Conditional Formatting - More than 3 Options

[chickalina]

I have a database that has information for the past 5 years and will continue
on. I need to color code the background of the box depending on the value of
the year in the box. The text box is labeled YearAdded.

For instance:
If the value in the field is 2006, the background is yellow
If the value in the field is 2007, the background color is blue

and so on...

I've seen so many posts, but I haven't found one that is able to do this.

Can anyone help?

 

[Marshall Barton]

I have a database that has information for the past 5 years and will continue
on. I need to color code the background of the box depending on the value of
the year in the box. The text box is labeled YearAdded.

For instance:
If the value in the field is 2006, the background is yellow
If the value in the field is 2007, the background color is blue

and so on...

You can use CF in a convoluted way to do that.

First, make the year text box's BackStyle Transparent.

Then add another text box the same size and position as the
year text box (use Format - Send to Back to put it behind
the year text box). Set this new text box's BackStyle to
Transparent too.

Now, download and install the special font at:
http://www.mvps.org/access/forms/frm0055.htm

Set this text box's FontName to LaBox. Set the control
source expression to this kind of thing:
=IIf([YearAdded] Between 2008 And 2010,"AAAAAA",Null)
Now you can use CF on this text box to deal with different
colors for those 3 years.
Expression Is: [YearAdded] = 2008
ForeColor: orange

Expression Is: [YearAdded] = 2009
ForeColor: purple

Expression Is: [YearAdded] = 2010
ForeColor: pink

To get colors for another 3 years, repeat all that with
another text box using 2005, 2006 and 2007.

 

[chickalina]

Thanks for the suggestion... but there is no IF... THEN statement I can use?

This seems rather labor intensive for a simple database and a simple problem
since this DB will be used by many people across the country and I cannot
expect them all to install the font.
M

I have a database that has information for the past 5 years and will continue
on. I need to color code the background of the box depending on the value of
the year in the box. The text box is labeled YearAdded.

For instance:
If the value in the field is 2006, the background is yellow
If the value in the field is 2007, the background color is blue

and so on...

You can use CF in a convoluted way to do that.

First, make the year text box's BackStyle Transparent.

Then add another text box the same size and position as the
year text box (use Format - Send to Back to put it behind
the year text box). Set this new text box's BackStyle to
Transparent too.

Now, download and install the special font at:
http://www.mvps.org/access/forms/frm0055.htm

Set this text box's FontName to LaBox. Set the control
source expression to this kind of thing:
=IIf([YearAdded] Between 2008 And 2010,"AAAAAA",Null)
Now you can use CF on this text box to deal with different
colors for those 3 years.
Expression Is: [YearAdded] = 2008
ForeColor: orange

Expression Is: [YearAdded] = 2009
ForeColor: purple

Expression Is: [YearAdded] = 2010
ForeColor: pink

To get colors for another 3 years, repeat all that with
another text box using 2005, 2006 and 2007.

 

[chickalina]

Also, the AAAAAA is in the box and the color only shows when you are in the
box, it's not a permanent thing...

I know I'm doing something wrong, just can't figure out what.

Thanks so much!

I have a database that has information for the past 5 years and will continue
on. I need to color code the background of the box depending on the value of
the year in the box. The text box is labeled YearAdded.

For instance:
If the value in the field is 2006, the background is yellow
If the value in the field is 2007, the background color is blue

and so on...

You can use CF in a convoluted way to do that.

First, make the year text box's BackStyle Transparent.

Then add another text box the same size and position as the
year text box (use Format - Send to Back to put it behind
the year text box). Set this new text box's BackStyle to
Transparent too.

Now, download and install the special font at:
http://www.mvps.org/access/forms/frm0055.htm

Set this text box's FontName to LaBox. Set the control
source expression to this kind of thing:
=IIf([YearAdded] Between 2008 And 2010,"AAAAAA",Null)
Now you can use CF on this text box to deal with different
colors for those 3 years.
Expression Is: [YearAdded] = 2008
ForeColor: orange

Expression Is: [YearAdded] = 2009
ForeColor: purple

Expression Is: [YearAdded] = 2010
ForeColor: pink

To get colors for another 3 years, repeat all that with
another text box using 2005, 2006 and 2007.

 

[Marshall Barton]

Thanks for the suggestion... but there is no IF... THEN statement I can use?

This seems rather labor intensive for a simple database and a simple problem
since this DB will be used by many people across the country and I cannot
expect them all to install the font.

It definitely is convoluted, but I have not found any other
way to get more than four colors in a text box on a
continous form.

The reason you can not use VBA code for this kind of effect
is because a text box only has one set of properties, so
when you set a property in code, it applies to all the
"rows" in the form.

Before Conditional Formatting was introduced, we had to
stack a separate text box for each different color. With
CF, we can use 3 colors on one text box. I just ran a quick
check in A2010 Beta and the CF limit seems to have been
raised to substantially more than 3 so the convoluted games
may come to an end.

 

[Marshall Barton]

Also, the AAAAAA is in the box and the color only shows when you are in the
box, it's not a permanent thing...

I know I'm doing something wrong, just can't figure out what.

There are several text boxes involved, which one are you
talking about?

Did you set the BackStyle of all of the text boxes to
Tranparent?

Note that the data text box should display it's own
BackColor when it has the focus (that's the way Transparent
works). It should display the CF color from the other text
boxes on all the other rows.

 

[chickalina]

Here is what I'm using:

YearAdded - is the text box where the year gets entered by a user

In the first text box (which is set to transparent):

=IIf([YearAdded] Between 2008 And 2010,"AAAAAA",Null)

I then apply conditional formatting for each of the years:

2008, 2009 and 2010

It displays both the 2008 and the AAAAAA in the box, one on top of the
other. It also does not show the color unless the cursor is in the year added
text box.

So am I supposed to replace the AAAAAA with something?

Thanks!
M

 

[Marshall Barton]

Here is what I'm using:

YearAdded - is the text box where the year gets entered by a user

In the first text box (which is set to transparent):

=IIf([YearAdded] Between 2008 And 2010,"AAAAAA",Null)

I then apply conditional formatting for each of the years:

2008, 2009 and 2010

It displays both the 2008 and the AAAAAA in the box, one on top of the
other. It also does not show the color unless the cursor is in the year added
text box.

So am I supposed to replace the AAAAAA with something?

It sounds like you did not install the LaBox font and/or did
not set the "other" text box's FontName to LaBox.

The "AAAAA" is the largest solid block character in the font
and should fill the "other" text box's entire area. If
there is an uncolored area on the right side if the text
box, add more AAA to the string. If there is an uncikired
aread at the top/bottom make the FontSize bigger.

All of the text boexes involved in this process need to have
their BackStyle set to Transparent.

 

[chickalina]

Thank you for the information Marshall.... I don't want to sound like a pain,
but the only problem why I cannot use the LABox font is because I don't know
who all will be using this application in our offices across the country, so
I will not be able to install on every machine. Is there a way around this?

Here is what I'm using:

YearAdded - is the text box where the year gets entered by a user

In the first text box (which is set to transparent):

=IIf([YearAdded] Between 2008 And 2010,"AAAAAA",Null)

I then apply conditional formatting for each of the years:

2008, 2009 and 2010

It displays both the 2008 and the AAAAAA in the box, one on top of the
other. It also does not show the color unless the cursor is in the year added
text box.

So am I supposed to replace the AAAAAA with something?

It sounds like you did not install the LaBox font and/or did
not set the "other" text box's FontName to LaBox.

The "AAAAA" is the largest solid block character in the font
and should fill the "other" text box's entire area. If
there is an uncolored area on the right side if the text
box, add more AAA to the string. If there is an uncikired
aread at the top/bottom make the FontSize bigger.

All of the text boexes involved in this process need to have
their BackStyle set to Transparent.

 

[Marshall Barton]

Thank you for the information Marshall.... I don't want to sound like a pain,
but the only problem why I cannot use the LABox font is because I don't know
who all will be using this application in our offices across the country, so
I will not be able to install on every machine. Is there a way around this?

Did you read my article about all the reasons why other
fonts are way less than ideal? If you want to find a simple
non solid block font solution, you will have to wait for
A2010 to come out and hope that the new CF doesn't get cut
from the final release..

Note that, unlike most other fonts, LaBox is in the public
domain so you can send it to your users without licensing
concerns. I never had a distribution problem like yours,
but there are lots of other people that are knowledgable
about using install scripts to make sure each user has all
needed files. Of particular note is Tony's very popular
Auto FE Updater at http://autofeupdater.com/

 

[chickalina]

OK... I read what you wrote again and I did what you said to do with the
transparent and changing the font. I have a colored box now, but it's black,
not the colors I indicated in CF.
I'm still going to have problems with undefined users though.
M

 

[Marshall Barton]

OK... I read what you wrote again and I did what you said to do with the
transparent and changing the font. I have a colored box now, but it's black,
not the colors I indicated in CF.

Black? Where did that come from? Maybe you set the
BackColor in CF instead of the ForeColor as I said?

 

[chickalina]

The backcolor doesn't remain... it disappears when you try and type a new
year in the text box. You have to completely exit out of Access and come back
in. When you edit it again, the same thing happens.

I removed the As in between the "AAAAA" and now they are gone.

I have a database that has information for the past 5 years and will continue
on. I need to color code the background of the box depending on the value of
the year in the box. The text box is labeled YearAdded.

For instance:
If the value in the field is 2006, the background is yellow
If the value in the field is 2007, the background color is blue

and so on...

You can use CF in a convoluted way to do that.

First, make the year text box's BackStyle Transparent.

Then add another text box the same size and position as the
year text box (use Format - Send to Back to put it behind
the year text box). Set this new text box's BackStyle to
Transparent too.

Now, download and install the special font at:
http://www.mvps.org/access/forms/frm0055.htm

Set this text box's FontName to LaBox. Set the control
source expression to this kind of thing:
=IIf([YearAdded] Between 2008 And 2010,"AAAAAA",Null)
Now you can use CF on this text box to deal with different
colors for those 3 years.
Expression Is: [YearAdded] = 2008
ForeColor: orange

Expression Is: [YearAdded] = 2009
ForeColor: purple

Expression Is: [YearAdded] = 2010
ForeColor: pink

To get colors for another 3 years, repeat all that with
another text box using 2005, 2006 and 2007.

 

[chickalina]

I deleted the original text boxes I made and created new ones from scratch.
They all work now....

Changed all the BackStyles to Transparent (even the "yearadded" text box
where the information is entered)
Changed the font to LABox
Used the IIF statement for the three conditions and the AAA to designate the
length of the color
Set the three expressions in CF to the [yearadded] text box and changed the
forecolor.

Everything is working perfectly now.... thank you so much for your help and
your patience!
M