Crystal Report Formula Help Needed

Options
Can anyone help? We need to report on some Governance people. Some have Date To as blank, and some have a date. We want the ones with the date to blank or greater than CurrentDate to show up as "Current" and those with the Date To as less than CurrentDate show up as "Past."


I am so rusty in Crystal now. Can anyone nudge me in the right direction?


 
Tagged:

Comments

  • Hi Amy!  Not sure if this is the exact syntax but off the top of my head it should be something like this:


    If ((IsNull({Date_To_Field_Name}) or ({Date_To_Field_Name} > CurrentDate)) then "Current" else "Past"


    Give that a try and let us know.  Or just wait for an answer from Alan or James or John or... :)


     
  • Is it a real date field (like a Gift Date), or a fuzzy date field (like a Constituency Date)?  Is your data source an Export to Blackbaud Report Writer database, another Export format, or a direct ODBC connection to the live data?
  • It is a real date field (Constituency Code Date To."

    The data source is an export to .mdb


    Trying:

    If ((IsNull({CnCnstncy_1.CnCnstncy_1_Date_To}) or ({CnCnstncy_1.CnCnstncy_1_Date_To} > CurrentDate)) then "Current" else "Past"


     as Joshua suggested, but it is saying a ) is missing.


    Thanks for your help, everyone!

     
  • The dates show in the report as: Thursday, September 15, 2015, as an example.

     
  • John Heizer:

    Is it a real date field (like a Gift Date), or a fuzzy date field (like a Constituency Date)?  Is your data source an Export to Blackbaud Report Writer database, another Export format, or a direct ODBC connection to the live data?

    Oh...ugh... wrong. Fuzzy Date field. Except we put month, day and year in. Now I feel dumb. Still can't figure out that blasted formula though.

  • If ((IsNull({Date_To_Field_Name}) or ({Date_To_Field_Name} > CurrentDate)) then "Current" else "Past")


    That should fix the syntax error.  Be aware that if you have some of those dates that are just a year or just a month and year then you may get erratic results.
  • OK, with the help of Ron at Blackbaud we got this sorted. Crystal was not reading the Cons Code Dates as Dates, so we had to make a conversion formula to translate it into dates:


    If InStr({CnCnstncy_1.CnCnstncy_1_Date_To},",") <> 0 then

      Right({CnCnstncy_1.CnCnstncy_1_Date_To}, Length({CnCnstncy_1.CnCnstncy_1_Date_To}) - (InStr({CnCnstncy_1.CnCnstncy_1_Date_To},",")+1))

    else

      ""


    Then used this formula to make it "Past" or "Current":

    If {@DateToConvert} = "" 

    then

      "Current"

    else if isdate({@DateToConvert}) then

     (

       if Date({@DateToConvert}) > CurrentDate

       then "Current" else "Past"

     )

    else

      "test"


    Thanks for your help everyone!!!
  • Joshua Bekerman bCRE:

    Or just wait for an answer from Alan or James or John or... :)

    Damn I hate being a foregone conclusion laugh


    Amy, in case this didn't come up in your discussion with Ron, if you go to Field Explorer and right-click on a field, then click on Show Field Type, Crystal will show you whether your date field (or any other field) is being treated as a DateTime or String (or Number/Currency/Boolean etc.) data type. Might be helpful to see which fields you have to convert using your formula.

  • Alan French:

    Joshua Bekerman bCRE:

    Or just wait for an answer from Alan or James or John or... :)

    Damn I hate being a foregone conclusion laugh

     

    LOL, right?


    Ron solves all problems!

Categories