CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2013
    Posts
    7

    IIF () or If Then Else?

    Hello,

    I have three fields Create Date, Consult Date, and Diag Date. I want to create a formula field that will test if first, if Diag Date is populated, then the value of the formula field will be the value of Diag Date, If Diag Date is null the it will see if Consult Date is populated. If so then the value will be the same as Consult Date. If Consult Date is null the the value will be that of Create Date. I have tried both an IIF() function and an If - Then - Else statement to achieve this but I only get the value of Diag Date when I run the report. The IIF function is: IIf(Not IsNull({@Diag Date}),{@Diag Date},IIf(Not IsNull({@Consult Date}),{@Consult Date},{@Create Date}))
    and the If Then Else statements is: If IsNull({@Diag Date}) then
    {@Diag Date} else if
    IsNull({@Consult Date}) then
    {@Consult Date} else
    {@Create Date}

    Any help with this would be greatly appreciated!!

  2. #2
    Join Date
    Jul 2005
    Posts
    1,083

    Re: IIF () or If Then Else?

    Welcome to CodeGuru forums!
    Code:
    If Not IsNull({@Diag Date}) Then {@Diag Date} 
    Else 
    If Not IsNull({@Consult Date}) Then {@Consult Date} 
    Else
    {@Create Date}
    If it's Crystal Reports then it's important to see:
    menu/Options/Reporting/ReadingData/Convert Database Values to Default
    If it is not checked then you could test with IsNull
    If it is checked then you should test with: Date({table.datefield}) = Date(0,0,0)
    JG


    ... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...

  3. #3
    Join Date
    Feb 2013
    Posts
    7

    Re: IIF () or If Then Else?

    Hi,

    Thank you for your response. With the code that you gave me, only the value of the {@Diag Date} field populates the formula field where all three fields used in the code are populated. Otherwise it is null.

  4. #4
    Join Date
    Jul 2005
    Posts
    1,083

    Re: IIF () or If Then Else?

    You are evaluating formula fields and only you know the code : {@Diag Date}, {@Consult Date} and {@Create Date}
    So share with us and post the code of the formulas
    JG


    ... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...

  5. #5
    Join Date
    Feb 2013
    Posts
    7

    Re: IIF () or If Then Else?

    All three formula fields, {@Diag Date}, {@Consult Date} and {@Create Date}, use the date() function to pull the date part of three date-time fields, DX_Dtm, Diag_DTtm, and Create_DTtm. So they are date(DX_Dtm), date(Diag_DTtm), and date(Create_DTtm). I used the original fields in the formula and had the same results.

  6. #6
    Join Date
    Feb 2013
    Posts
    7

    Re: IIF () or If Then Else?

    The IIF() function and the if then else statement both seem to test the first part of the code, If Not IsNull({@Diag Date}) Then {@Diag Date}, and it does not go on from there.

  7. #7
    Join Date
    Jul 2005
    Posts
    1,083

    Re: IIF () or If Then Else?

    The IIF() function and the if then else statement both seem to test the first part of the code, If Not IsNull({@Diag Date}) Then {@Diag Date}, and it does not go on from there
    Because the formula field is not null

    Apply the formula from post #2 to original table fields
    But before enter to : menu/Options/Reporting/ReadingData/Convert Database Values to Default
    Uncheck it and you could test with IsNull
    Check it and you must test with: Date({table.datefield}) = Date(0,0,0)
    JG


    ... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...

  8. #8
    Join Date
    Feb 2013
    Posts
    7

    Re: IIF () or If Then Else?

    If Convert Database Values to Default is not checked and I use: If Not IsNull({@Diag Date}) Then {@Diag Date}
    Else
    If Not IsNull({@Consult Date}) Then {@Consult Date}
    Else
    {@Create Date} it is still the same,
    If it is checked and I use it by checking with date(0,0,0) it pulls only the value of {@Create Date}
    as the value of the formula field where all three are populated.

  9. #9
    Join Date
    Feb 2013
    Posts
    7

    Re: IIF () or If Then Else?

    "Check it and you must test with: Date({table.datefield}) = Date(0,0,0) " It should be <> Date(0,0,0). It worked then. Thank you very much for your help!

  10. #10
    Join Date
    Feb 2013
    Posts
    7

    Re: IIF () or If Then Else?

    I can't find the thumbs up on the thread tools menu, sorry!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured