Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF problem (A2000 9-0-3821SR1)

    In, IIF[txtPrepaidPP]=<font color=448800>PP</font color=448800>[TotalPreapid]=[TotalFreight], PP should be "PP" since its text, you also need to have commas seperating your expression from your true and false parts.

    i don't know if i have the true part of the expression right, but this should atleast get you a little further.

    IIF([txtprepaidPP]="PP", [txtPrepaidPP]=[TotalFrieght], "")
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: IIF problem (A2000 9-0-3821SR1)

    John almost got you there, but his expression needs a little tweak. The control source should begin with =, the = should not be somewhere within the expression. And you must also specify a value to be returned if the condition is not true. If you want TotalPrepaid to be blank if txtPrepaidPP is not "PP":

    <code>=IIf([txtPrepaid]="PP",[TotalFreight],Null)</code>

    or if you want it to be 0:

    <code>=IIf([txtPrepaid]="PP",[TotalFreight],0)</code>

  3. #3
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF problem (A2000 9-0-3821SR1)

    thanks Hans, i thought the = in the expression was wrong <img src=/S/doh.gif border=0 alt=doh width=15 height=15>
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  4. #4
    New Lounger
    Join Date
    Feb 2005
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IIF problem (A2000 9-0-3821SR1)

    Good afternoon

    Among other fields on a form I have

    1 = unbound field, named txtPrepaidPP
    2 = TotalFreight (calculated field)
    3 = TotalPrepaid

    If freight charges are to be prepaid a user would type PP into the txtPrepaidPP field

    What I have tried to do in the TotalPrepaid field is to return the value of the TotalFreight field if PP is typed into the txtPrepaidPP field

    To do this I have typed the folwoing into the control source of the TotalPrepaid field

    IIF[txtPrepaidPP]=PP[TotalPreapid]=[TotalFreight] This however is not working, can anybody give me an idea of where I am going wrong

    TIA

  5. #5
    New Lounger
    Join Date
    Feb 2005
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF problem (A2000 9-0-3821SR1)

    Thank you

    I have changed the fields slightly to be in line with my own fields

    =IIf([txtPrepaidPP]="PP",[TotalPrepaid]=[TotalFreight],Null)

    However the TotalPrepaid field returns a #Name error, I have made sure that each of the fields are named correctly, is it apparent what else I may have done wrong.

    Thanks

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IIF problem (A2000 9-0-3821SR1)

    Look carefully at your expression. The "then" portion is written as [TotalPrepaid]=[TotalFreight], which is not the name of a field, so you get a Name# error. If this entire expression is the datasource for TotalPrepaid, then it should be written as:

    =IIf([txtPrepaidPP]="PP, [TotalFreight],Null)
    Charlotte

  7. #7
    New Lounger
    Join Date
    Feb 2005
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF problem (A2000 9-0-3821SR1)

    Thank you Charlotte, however I do not understand how that will populate the TotalPrepaid field. Without all of the if's etc. what I am trying to do is to say that if PP is entered into the txtPrepaidPP field the value in the TotalFreight field will be shown in the TotalPrepaid field, if PP is not entered into the txtPrepaidPP field the TotalPrepaid field wil be left blank.

    Unles I am totally confused I must somehow mention all 3 fields in the expression to make it work, that is why I though it would be something like the example that I psted that referenced all 3 fields.

    Thanks

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,265
    Thanks
    2
    Thanked 187 Times in 173 Posts

    Re: IIF problem (A2000 9-0-3821SR1)

    Hi Pauline,
    As you stated in your first post, this formula goes into the ControlSource of the TotalPrepaid field. It effectively says that if txtPrepaidPP equals "PP" then TotalPrepaid equals TotalFreight, otherwise TotalPrepaid is null. If you actually put the formula into the controlsource of TotalPrepaid, you should see that it works as indicated. Note: Charlotte's post is missing a final quotation mark after PP.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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