1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Help with IIF in Access - want to include AND?

Discussion in 'Business Applications' started by NannyMcPhee, Jan 19, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. NannyMcPhee

    NannyMcPhee Thread Starter

    Joined:
    Jan 19, 2013
    Messages:
    3
    Hello - I have been trawling your IIF and Nested IIF threads looking for an answer to my question but can't find one! Is it possible to include AND in an IIf? I want to Sum if multiple conditions are met. I suspect I might need to use nested iif but I was under the impression nested was more for OR conditions? May be wrong?! It's been a long time since I've worked with expressions in Access! Wish I was doing this in Excel!?!?!:(

    Here's my example:

    In my subform I have a cost for a red jumper, blue jumper and yellow jumper and I have a condition which is a yes/no field to say whether my customers have paid or not. I also have a textfield with the colour of the jumper that has been ordered. The form is based on a query. So I want to say:

    if([Order paid]=False AND [colour selected]="red",sum([red colour jumper cost] and so on to include all colours and sum of all prices.... ideally I only want to display this field when there is a cost to be displayed....

    I hope this makes sense? I've been tearing my hair out with this trying multiple versions with and without nesting - hope you can help! :eek:
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    NannyMcPhee, welcome to the forum.
    Do you mean that you want to sum each Colour separately?
    If so you would need a text box/query column for each colour, unless you filter the records by colour first.
    Using VBA code in a form is much more flexible than using iif() in a query, but the query IIF can use the And operator.
    You can also use VBA code within a query.
     
  3. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Here is a database with a query using And.
     

    Attached Files:

  4. NannyMcPhee

    NannyMcPhee Thread Starter

    Joined:
    Jan 19, 2013
    Messages:
    3
    :D

    Thank you and thank you for including an example - extremely helpful!
     
  5. NannyMcPhee

    NannyMcPhee Thread Starter

    Joined:
    Jan 19, 2013
    Messages:
    3
    Hello OBP - so i've tried your suggestion and added to it a little to give me multiple outcomes - but it still won't work! Arghhh! It just shows "0" rather than throw up any error message so I think i'm getting closer! Each of the fields named are present in a query underlying my subform and I want to display the following in a textbox on the subform rather than in my query if poss - or does it have to be in my query? -

    =IIf(IsNull([Paid?]) And [ColourOrdered]="Red",Sum([RedFee]),IIf(IsNull([Paid?]) And [ColourOrdered]="Green",Sum([GreenFee]),IIf(IsNull([Paid?]) And [ColourOrdered]="Blue",Sum([BlueFee]),0)))
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Are you sure that the Colours are stored as Text, if they are combo selections they could be stored as numeric values.
    I would try to get just one colour working, then when you get that working copy it in to the formula and change the colours as required.
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1085970

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice