Skip to Content

Crystal Reports: background highlighting not consistently working

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?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Apr 24 at 07:04 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Apr 24 at 06:56 PM

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

    -Dell

    Add comment
    10|10000 characters needed 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