How -Display record in different color based on date 1

[Donzi255]

I would like to print the output of query in a different color (Red) when the an expiry date field is within the next 3 months. Field names are from Asset Table & Employee Table: Last Name, 1stName; Model, s/n, Expiry date. If expiry date = 11/30/08 - January 31, 2009 print Expiry Date in RED; which I can do with this statement:
If currentfieldvalue =Next61To90Days then crRED else defaultAttribute)

How do I print ALL other fields in this record RED ?

CRXI & SQL 2003.
Thanks
Donzi255

 

[kskid]

Hold the control key and left click on each object you want red and then left click -> format object and enter the same formula you used.

 

[lbass]

If you really are looking at the next three months, you should be using:

if currentfieldvalue in currentdate to currentdate + 90 then
crRed else
crdefaultattribute

-LB

 

[Donzi255]

Althought this worked, it turned all records to red. So, I'll explain further. I want to produce a report of all expiring assets in the next 3 years by month end date. I want to alert the manager of his employee assets expiring in the next 3 months with color change. All other assets will be default color of black. Example:

Mickey Mantle PC expires 11/30/2008
Mickey Mantle Monitor expires 06/30/2010
Micky Mantle Laptop expires 12/31/2008

Change Nov/Dec 2008 to red and keep 2010 black.

Thanks

 

[lbass]

What formula did you use and where did you put it? The formula I suggested would not have colored all records red.

-LB

 

[Donzi255]

I re-checked and made a mistake. I am printing only the requested end date field in red. I highlighted all other fields format object and created the formula in the Formula workshop - current formula:Font
If {Asset_Detail.Asset_End_Dat} = Aged61To90Days then crRed else
defaultattribute

 

[lbass]

I don't really know what you are saying. Why are you using Aged61to90days? I also can't tell whether you want only the one field in red, or all fields in the detail section.

-LB

 

[Donzi255]

Sorry for the confusion. I do not want all records in the detail section red. I only want the record in red, if it's within the specified date range. I used Aged61to90days because it is in the drop down list.

 

[lbass]

Do you want ALL fields in one row to appear in red or just the one field?

Aged61to90days is used to compare a date with the currentdate to test if the date is 61 to 90 days BEFORE the currentdate, so I don't think that's what you want. I thought you wanted the NEXT 90 days from today.

-LB

 

[Donzi255]

Yes, all the fields in the row to appear RED. And, I do want the next 90 days from current month end.

 

[lbass]

Then select all fields->right click->format objects->font->x+2 and enter:

if currentfieldvalue in dateadd("m",1,currentdate-day(currentdate))+1 to
dateadd("m",1,currentdate-day(currentdate))+90 then
crRed else
crdefaultattribute

Copy this and paste it into the formula area.

-LB

 

[Donzi255]

Perfect !!! That was it !!! Although, I did have to substitute my end_date field with currentfieldvalue.

Thank You lbass !!!

Donzi255

 

[lbass]

If the field you want to color IS the end_date field, then currentfieldvalue should have worked.

-LB

 

[lbass]

Oops, sorry, I had already forgotten you were wanting to color all fields, so you are right, you needed to change that to the date field.

-LB

 

[Donzi255]

I received an error using currentfieldvalue, "Date Time is Required here"

 

[lbass]

Yes, you were right to change it to the actual date in this case, since you were trying to format fields of different datatypes based on the value of a specific date field.

-LB