Avatar of Jeff S
Jeff S
Flag for United States of America asked on

Crystal Reports - Conditional Formatting based on Results

I have three fields in my crystal report that I need to conditionally format.

Field #1 - LastDental (field type is String).

Per my clients request, please bold and increase font by 2 points (currently set to Arial 8 regular) when the LastDental < Today - 365 days or equals "Never".

Field #2 - HGBA1CValue (field type is String).

Per my clients request, please bold and increase font by 2 points (currently set to Arial 8 regular) when HGBA1CValue > 9 or equals "Never".

Field #3 - SMGDate (field type is String).

Per my clients request, please bold and increase font by 2 points (currently set to Arial 8 regular) when the SMGDate < Today - 365 days or equals "Never".
Crystal Reports

Avatar of undefined
Last Comment
Jeff S

8/22/2022 - Mon
Mike McCracken

Basic method for font size
Behind the formula button for font size
If {YourField} > 9 then
   10
Else
    8

To bold - behind the formula button for style
If {YourField} > 9 then
    crBold
Else
   crRegular


Are the dates strings or is that the field you are displaying?
If strings what format do they have?

mlmcc
GJParker

You can do this by right clicking on the respective fields -> go to the Font tab and add conditional formulas against each of the properties you want to change.

HTH
format.png
Jeff S

ASKER
mlmcc -

The dates are Strings. They are displayed as MM/DD/YYYY. Does that help?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
GJParker

You shoudl be able to use this for the font size

Local NumberVar Y := Val(Split({@LastDental}, '/')[3]);
Local NumberVar M := Val(Split({@LastDental}, '/')[1]);
Local NumberVar D := Val(Split({@LastDental}, '/')[2]);

If Date(Y,M,D) < Today - 365 Or {@date} = 'Never' Then
    10
Else
    8

and swithc to crBold and crRegular for the other change

HTH
gavsmith

So like mlmcc said but use:

If CDate({LastDental}) < DateAdd("d", -365, CurrentDate) or {LastDental} = "Never" then
   10
Else
    8

Do the same for SMGDate and you are best converting HGBA1CValue to a number before comparing as strings do compare differently:

If CDbl({HGBA1CValue}) > 9 or {HGBA1CValue} = "Never" then
   10
Else
    8

Jeff S

ASKER
I used:

If CDate({rptApptsByUser;1.LastDental}) < DateAdd("d", -365, CurrentDate) or {rptApptsByUser;1.LastDental} = "Never" then
   10
Else
    8
 
and

If CDate({rptApptsByUser;1.LastDental}) < DateAdd("d", -365, CurrentDate) or {rptApptsByUser;1.LastDental} = "Never" then
   crBold
Else
    crRegular

and getting back an error at run time that indicates: "Bad Date Format String".
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Mike McCracken

To use them directly they must be in YYYY/MM/DD format.  Did you try the GJParker method?

mlmcc
Jeff S

ASKER
GJParkers method gives me this error at runtime:

"A subscript must be between 1 and the size of the array"

I have no clue what this means.
Mike McCracken

What is the actual data in the field not how it is displayed?

mlmcc
Your help has saved me hundreds of hours of internet surfing.
fblack61
Mike McCracken

The error indicates
1.  You have some data that is in the wrong format or is null
2.  The dates are stored in a format and displayed as DD/MM/YYYY

mlmcc
SOLUTION
GJParker

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Jeff S

ASKER
I am getting a message that the indicates: "The remaining text does not appear to be part of the formula" and it highlights everything from the ELSE down to the 8.
GJParker

remove the semi colon before the else
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Jeff S

ASKER
Getting so close ... getting this now. "The string is non-numeric"
GJParker

i'm guessing that one of your dates isn"t in the correct format and one of you date par
ts cant be converted to a number.
Jeff S

ASKER
I conditionally formatted them in SQL and all should be MM/DD/YYYY or "Never".
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Mike McCracken

Try ir this way

Local NumberVar Y;
Local NumberVar M;
Local NumberVar D;

If  InStr({@LastDental},'/') > 0 Then
(
Local NumberVar Y := Val(Split({@LastDental}, '/')[3]);
Local NumberVar M := Val(Split({@LastDental}, '/')[1]);
Local NumberVar D := Val(Split({@LastDental}, '/')[2])
);
Else
(
Local NumberVar Y := 1900;
Local NumberVar M := 1;
Local NumberVar D := 1
);

If Date(Y,M,D) < Today - 365 Then
    10
Else
    8


mlmcc
Jeff S

ASKER
mlmcc,

I am back to "The string is non-numeric". I am stumped.
Jeff S

ASKER
mlmcc and GJParker -

Please accept my apology. I had the conditional fomatting on the HGBA1CValue I asked for help on earlier and didn't realize I still had that on. I was going through and making changes and testing these one by one and found your coding was right. The issue is with the suggestion gavsmith provided earlier. I am getting the "The string is non-numeric" on this:

If CDbl({rptApptsByUser;1.HGBA1CValue}) > 9 or {rptApptsByUser;1.HGBA1CValue} = "Never" then
   10
Else
    8
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Mike McCracken

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Jeff S

ASKER
Split Points to be Fair. Thanks a million!!