Calculated Query with IIF, AND & ELSE Conditions (1 Viewer)

[Reese]

[Solved] Calculated Query with IIF, AND & ELSE Conditions

So, there may already be an answer for this somewhere but I can't find it.

I know it's possible to create a calculation query that uses different calculations based upon different variables using an IIF statement. I've done it before using the following and it works fine:

(IIf([Cost_Category]="Full Price",Sum(nz([Program_Cost])+nz([Millage_Fee])+nz([Auditorium_Cost])+nz([Cancel_Fee])),IIf([Cost_Category]="Discount",Sum(nz([Program_Cost])+nz([Millage_Fee])+nz([Auditorium_Cost])+nz([Cancel_Fee])),IIf([Cost_Category]="Swap",Sum(nz([Program_Cost])+nz([Millage_Fee])+nz([Auditorium_Cost])+nz([Cancel_Fee])),IIf([Cost_Category]="Donation",Sum(nz([Program_Cost])+nz([Millage_Fee])+nz([Auditorium_Cost])+nz([Cancel_Fee])),IIf([Cost_Category]="BOCES",Sum(nz([Cost_Per_Person])*nz([BOCES_Number_of_Participants])))))))) AS Total_Cost

I would like to know if I can integrate AND and ELSE functions into the SQL at the same time. I have two goals in this:

-Simplify the above SQL so it's along the lines of IIF([Cost_Category]="BOCES",Sum( insert calculation here), ELSE Sum( insert different calculation here) AS Total_Cost

-Create a new query that has something along the lines of IIF([Canceled]="-1" AND [Paid]="0",( set Total_Cost as "0"), ELSE Sum( insert calculation here) AS Total_Cost

Is something like this possible? Thanks!

 

[David R]

IIF already has an Else clause. http://office.microsoft.com/en-us/access-help/iif-function-HA001228853.aspx

You can do ANDs with parentheses.

So probably something like:

Total_Cost: IIF([COLOR="Blue"]([Canceled]="-1" AND [Paid]="0")[/COLOR],[COLOR="Green"]"0"[/COLOR],[COLOR="Red"]Sum( insert calculation here)[/COLOR])

 

[Reese]

Ok, thanks. I'll play around with it and see what I can do.

 

[Reese]

Thanks, David. I got the following code to work great:

(IIf(([Canceled]="-1" And [Paid]="0"),"0",Sum(nz([Program_Cost])+nz([Millage_Fee])+nz([Auditorium_Cost])+nz([Cancel_Fee])-nz([Reimbursed_Amount])))) AS Total_Cost

The only problem is now Access doesn't seem to allow formatting of the Actual_Cost field--i.e. I lost the currency formatting and when I look at the field's properties in Design view, the Format drop-down menu is completely blank.

The same thing happened when I simplified the query that I posted at the beginning of this thread (which, also calculated everything perfectly). Any idea why that is and how I can fix it?

 

[Reese]

Found the solution!

http://www.access-programmers.co.uk/forums/showthread.php?t=233270

Apparently putting quotes around 0 or paranthesis around the entire IIf statement makes Access see it as a string versus a number.

 

[David R]

Yup. Since you had "0" in your example I figured you were using a string variable for some reason.

Glad you got it working!