Help with an IIf funcition

Pettor

Board Regular
Joined
Aug 8, 2015
Messages
137
Hello guys,

Can someone give me a help with the function below?

Everything breaks when I am trying to enter the IIf after the minus symbol

VBA Code:
IIf([FieldName]=0,0,Nz(Sum(IIf([Table1].[Column1]="10" OR [Table1].[Column1]="20",”50”,”30”)))-IIf(ISNULL([Table1].[Column2]),0,SUM(IIf(ISNULL([Table1].[Column2]),0,1)))) AS TEST,
Basically, the question is, can I place an Iff function inside another IIf function?

Thanks
 
Last edited:

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Pettor

Board Regular
Joined
Aug 8, 2015
Messages
137
What I get as a reply is that "the query doesn't include the specified expression as part of an aggregate function"
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,066
Office Version
365
Platform
Windows
can I place an Iff function inside another IIf function?
Yes, you can, but I do see a few other issues with your formula.

1. If you are checking for numeric values, they should NOT be inside of double-quotes. Double-quotes indicate Text/string values. Unless those are numbers entered as Text, it should probably look like:
IIf([Table1].[Column1]=10

2. Access does not like slanted double-quotes (there is a difference.
" is OK
” is NOT OK

There may be other issues too, but those two stick out.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,066
Office Version
365
Platform
Windows
My advice when building complicated formulas is not to try to do it all at once. Start with a simplistic piece of it. Once you get that working, add another piece to it.
Once you get that working, then add the next layer of complexity to it.
So you are building it, bit-by-bit, and you can see where things get off track.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,066
Office Version
365
Platform
Windows
OK. If you need help with this, we would probably need to see a samle of what the data table looks like, and the SQL code for the entire query, as it appears you are doing an Aggregate Query, so it is important to see how the rest of the query is written.
 

Forum statistics

Threads
1,089,220
Messages
5,406,929
Members
403,113
Latest member
ms_excel_recal_or_die

This Week's Hot Topics

Top