Help with an IIf funcition

Pettor

Board Regular
Joined
Aug 8, 2015
Messages
141
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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Pettor

Board Regular
Joined
Aug 8, 2015
Messages
141
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,949
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,949
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.
 

Pettor

Board Regular
Joined
Aug 8, 2015
Messages
141
Ok, I will keep searching what the problem is and I will revert. Thanks
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,949
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,167
Messages
5,442,785
Members
405,196
Latest member
rmuir12

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top