Topic: If Statement using colour Posted: 06 Nov 2014 at 6:22pm
Hi,
I have an expiry date on a qualification report. I wish the font to change color based on the rules from my SQL statement.
Not knowing Crystal syntax I'm having issues figuring this out.
Basically I have chosen to use the formula editor for this field under font.
Red = Expired
Orange = expires within 30 days of current date
Black = expires within 31 days to 60 days of expiry date
Could anyone assist in how I perform the syntax?
case when
employee_qualifications.date_expiry < GETDATE() then 'Red'
when (abs(DATEDIFF(D, employee_qualifications.date_expiry,GETDATE())) < 30 AND q2employee_qualifications.date_expiry > GETDATE()) then 'Orange'
when (abs(DATEDIFF(D, employee_qualifications.date_expiry,GETDATE())) between 31 and 60 AND employee_qualifications.date_expiry > GETDATE()) then 'Black'
else 'NA'
end as status
Sorry I just got around to doing this. And it didn't work. My qualifications that exist between 31 and 60 remain orange.
If I did something like this...
IF {EDLRptTrainingStatusReportLatestExpiryDate.date_expiry} <= today then crRed
ELSE IF {EDLRptTrainingStatusReportLatestExpiryDate.date_expiry} >DateAdd("d",31,CurrentDate),("d",60,CurrentDate) then crBlack else
ELSE IF DATEDIFF("d", {EDLRptTrainingStatusReportLatestExpiryDate.date_expiry} ,today) < 30 THEN RGB(255,128,0)
ELSE crGreen //What ever colour you want here.
It would work, but it doesn't limit it to 60 days.
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Posted: 19 Nov 2014 at 3:47am
IF DATEDIFF("d", {employee_qualifications.date_expiry} ,today) < 0 then crRed ELSE IF DATEDIFF("d", {employee_qualifications.date_expiry} ,today) in 0 to 30 THEN RGB(255,128,0) ELSE IF DATEDIFF("d", {employee_qualifications.date_expiry} ,today) in 31 to 60 THEN crBlack ELSE //YOur color choice here
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
