Help with an IIf funcition

[Pettor]

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

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

 

[Pettor]

What I get as a reply is that "the query doesn't include the specified expression as part of an aggregate function"

 

[Joe4]

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]

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.

 

[Pettor]

Ok, I will keep searching what the problem is and I will revert. Thanks

 

[Joe4]

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.