How to add multiple font colors on date fields (i.e. expiring and exp soon)

[Former Member]

I want a staff report to show items that have expired and those reports that are about to expire. For example:

if PLAN.END_DATE has expired/passed (based on current date/today), make red and/or

if PLAN.END_DATE is within 60 days or less (based on current date/today), make purple

How would I do this on Format Field > Font > Font Color X-2 ?

[ido_millet · Accepted solution]

Use an IF or Case statement and a Color() or RGB() function.

[Former Member · Accepted solution]

Ido Millet wrote:

Use an IF or Case statement and a Color() or RGB() function.

I can do one, but not multiple. for example, I can set the expiring one to red

if {PLAN.END_DATE}<today-1 then red , but I can't seem to add anything in addition to (i.e. expiring within 60 days or less)

[ido_millet · Accepted solution]

Select Today

case is < #1/1/2015# : crRed

Case #1/2/2015# : crpurple

Case is > #1/2/2015# : crblue

default: crBlack ;

[Former Member · Accepted solution]

Ido Millet wrote:

Select Today
case is < #1/1/2015# : crRed
Case #1/2/2015# : crpurple
Case is > #1/2/2015# : crblue
default: crBlack ;

Thank you . However, I was hoping for a more dynamic way of specifying this without static dates. I tried removing the dates+hash and replacing with CurrentDate or Today, and it just made everything purple.

[ido_millet · Accepted solution]

This was just an example.  You need to substitute your own conditions for the branching logic.

If you provide your own formula it would be easier to help you correct it.

[abhilash_kumar · Accepted solution]

Hi Dominic,

Try this code please:

If {PLAN.END_DATE} < currentdate then

     crRed

else if DateDiff("d", currentdate, cdate(2015,02,18)) IN [1 to 60] then

     crPurple

-Abhilash