Skip to Content
0

Crystal Reports: background highlighting not consistently working

Apr 24 at 06:44 PM

26

avatar image

I set up a formula to have dates highlighted if the dates are greater than a user prompt date. For example, if a user enters "02/01/2018", then all dates after this date should be highlighted and all dates prior to this date should stay as they are. For some reason, I'm getting mixed up results.

if {@Test date} > {?@lasttest} then crLime else crnocolor

{@Test date} is the date already in the database and {?@lattest} is the user prompt.

What am I missing here?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Dell Stinnett-Christy Apr 24 at 07:04 PM
1

Ok, the string is the issue. When sorting or comparing date strings, they work in string-order not date-order. So, you need to leave them as dates or change them to the format yyyyMMdd to get them to compare correctly.

-Dell

Show 2 Share
10 |10000 characters needed characters left characters exceeded

One is a date and another is a string, so I've been trying to convert the string into a date.

Would you mind suggesting me a date conversion formula? I've tried these below and I'm not getting much luck...

ToText(cDate({?@lasttest}),"dd-MMM-yyyy")
ToText(cDate({?@lasttest}),"dd-MMM-yyyy")
0

ToText converts a date to a string. You want to go the other way around and convert a string to a date. If the string date will always be in the format of dd-MM-yyyy, you can use either CDate({?@lasttest}) for Basic Syntax or Date(?@lasttest}) for Crystal Syntax.

-Dell

0
Dell Stinnett-Christy Apr 24 at 06:56 PM
1

What is the formula for {@Test date}? Is it a date or a datetime? Is {?@lasttest} a date or a date time?

-Dell

Show 1 Share
10 |10000 characters needed characters left characters exceeded

For {@Test date}, I've just tried"

Left ({FlowTest.lasttest}, 10)

And for {?@lattest}, I've just tried:

ToText(cDate({?@lasttest}),"dd-MMM-yyyy")

{@Test date} is a date, and {?@lattest} is a string

0