IF function with date field and string field Crystal Reports

35 pts.
Tags:
Crystal Reports
Crystal Reports XI
I have written a report with the following fields: claim.date -- date field claim.remarks -- string field claim.pay -- numeric field PROBLEM: The report-field (say, Bonus) is 'pay * 15%' within the given date range, but it should be (zero), when the string field {claim.Remarks} starts with "SL". I have written a formula which does't work. FORMULA: if {Claim.date} in (#10/01/2008# to #12/31/2008#) and Left({Claim.Remarks},2)<>"SL"  then {qryClaim.pay}* 0.15 else if {Claim.date} >= #01/01/2009# and Left({Claim.Remarks},2)<>"SL" then {Claim.pay} * 0.25 else 0 please help. Thanks in advance.

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Could you please let us know what exactly you mean by "doesn't work" ? Is the formula returning wrong results ? Is it producing an error ?
    69,920 pointsBadges:
    report
  • GVR
    Hello Carlosdl, The formula does't return any thing. Just showing blank in the report detail section. Thank you.
    35 pointsBadges:
    report
  • GVR
    Carlosdl, Actually, when the above formula incorporated to the report as it is, it returns 0 (zero) to the record where the string field {Claim.Remarks} starts with 'SL' only, and nothing returns to any other records. It doesn't calculates the first test condition in the IF argument. When tested in Formula Editor window, it says "no errors were found" in the dialog box.
    35 pointsBadges:
    report
  • Martinjamesward
    Why not break the formula down into two parts test for the string 'SL' first and then do the date based stuff i.e.
    if Left({Claim.Remarks},2)<>"SL" then
    
    if ({Claim.date} >= #01/01/2009#) then ({Claim.pay} * 0.25) else
    ({qryClaim.pay}* 0.15)
    
    Else 
    0
    This way you can substitute the date test for a text string to see if the test for the 'SL' string is working - if thats OK then you need to look closer at the date comparison part
    415 pointsBadges:
    report
  • GVR
    Martin, I tried in this way also, but, the formula returning result of string part test only, but not date part test. More I tested the both the test conditions independently, the formulas working fine according to logic, when combining the both test logic, it returning only string test result. Thank you.
    35 pointsBadges:
    report
  • carlosdl
    I would try using the DateTime(YYYY,MM,DD) function to compare dates. if {Claim.date} in (DateTime(YYYY,MM,DD) to DateTime(YYYY,MM,DD)) and Left({Claim.Remarks},2)<>"SL" then {qryClaim.pay}* 0.15 ...
    69,920 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following