Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Unanswered: IIF with 2 criteria

    Hi,

    I need to have Sum of amount in a text box temporarily placed on the form using IIF statement. The Sum would be calculated if 2 of the columns in table satisfy the condition.

    The column in table on which I need to put condition has following data type field.

    1. SchemeNo = Numeric
    2. SchemeEnd = Yes/No

    How can I put 2 conditions with IIF statement?

    I tried following way, but it produces an Error. Even I tried to put SchemeEnd = False in the following statement. But didn’t work.

    =IIF([TxtDisplaySchemeNo]>0,[TxtSchemeOffer]-Nz(DSum("InvAmt","T_AdvPaySalesInvFooter","SchemeN o= TxtDisplaySchemeNo And SchemeEnd = 0"),0),"")


    Anyone have an idea about this?

    With kind regards,

    Ashfaque

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    try
    iif (isnumeric(Schemno) and SchemeEnd = "Yes/No",<true assignment>,<false assignment>)

    its possible that you cant use an IIF as a controls rowsource as design time, however you cna overcome this limitation by placing your code in a suitable event (such as firs on current event, before update, and relevant controls on change & lost focue events

    HTH

  3. #3
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks healdem,

    Thanks for the hint. I tried but it confused.

    The text box TxtDisplaySchemeNo shows the numerical value on the form ONLY after some actions otherwise it is null by default. Thats why I put "IF([TxtDisplaySchemeNo]>0".
    and on the basis of this I need to have sum of InvAmt field from table where the criteria would be "SchemeNo= TxtDisplaySchemeNo and SchemeEnd = False"

    These both fields (SchemeNo [Numeric type] and SchemeEnd [Yes/No type]) are from the same table called T_AdvPaySalesInvFooter.

    I can get the result keeping 2 text boxes containg 2 diff. IIF statement and then garher into one but it would be lengthy and surely will reduce the fuction's speed.

    I need to have both condtion in one IIF statement. So if the TxtDisplaySchemeNo would be greater than 0 it would check in table and get the sum.

    With kind regards,
    Ashfaque

Posting Permissions

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