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".
LVL 7
Jeff SAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
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
0
GJParkerCommented:
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
0
Jeff SAuthor Commented:
mlmcc -

The dates are Strings. They are displayed as MM/DD/YYYY. Does that help?
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

GJParkerCommented:
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
0
gavsmithCommented:
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

0
Jeff SAuthor Commented:
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".
0
mlmccCommented:
To use them directly they must be in YYYY/MM/DD format.  Did you try the GJParker method?

mlmcc
0
Jeff SAuthor Commented:
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.
0
mlmccCommented:
What is the actual data in the field not how it is displayed?

mlmcc
0
mlmccCommented:
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
0
GJParkerCommented:
Try this, I forgot to handle where the date = 'Never'

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

If {@LastDental} <> 'Never' 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

0
Jeff SAuthor Commented:
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.
0
GJParkerCommented:
remove the semi colon before the else
0
Jeff SAuthor Commented:
Getting so close ... getting this now. "The string is non-numeric"
0
GJParkerCommented:
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.
0
Jeff SAuthor Commented:
I conditionally formatted them in SQL and all should be MM/DD/YYYY or "Never".
0
mlmccCommented:
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
0
Jeff SAuthor Commented:
mlmcc,

I am back to "The string is non-numeric". I am stumped.
0
Jeff SAuthor Commented:
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
0
mlmccCommented:
You are converting the value and it may be NEVER
Try this

If {rptApptsByUser;1.HGBA1CValue} = "Never" then
    10
Else If CDbl({rptApptsByUser;1.HGBA1CValue}) > 9  then
   10
Else
    8

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeff SAuthor Commented:
Split Points to be Fair. Thanks a million!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.