Help with IIF in Access - want to include AND?

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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:
 

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.
 

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)))
 

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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Top