By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,773 Members | 2,307 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,773 IT Pros & Developers. It's quick & easy.

how to exclude specific cases within an IIf function

P: 5
Hi,

I'm trying to do the following query:


Expand|Select|Wrap|Line Numbers
  1. SELECT [PUTS-Q4].nbr_cusip, Sum([PUTS-Q4].SumOfamt_trd_fee) AS 
  2. SumOfSumOfamt_trd_fee, Sum((IIf([cd_trd_type]="I",[SumOfamt_par],"0"))) AS [amt par 1], Sum((IIf([Expr1]=2001,[SumOfamt_par],"0"))) AS Year_Par, (IIf([cd_trd_type]<>"I",[Year_Par],"0")) AS Year_Par1, Last([PUTS-Q4].Expr2) AS Issue_date, Last((DatePart("yyyy",[Expr2]))) AS Year_Issue, [Year_Par]/[amt par 1] AS [Put_%], [SumOfSumOfamt_trd_fee]*[Put_%] AS Put_amount, 2001 AS [Put date], (IIf([Put_%]<>"1",[Put_amount],"0")) AS Put_amt1
  3. FROM [PUTS-Q4]
  4. GROUP BY [PUTS-Q4].nbr_cusip, 2001
  5. HAVING ((([PUTS-Q4].nbr_cusip)="3133F0FW4"));
which works just fine without the following expression:

Expand|Select|Wrap|Line Numbers
  1. (IIf([cd_trd_type]<>"I",[Year_Par],"0")) AS Year_Par1
The purpose of this expression is to give me the [Year_Par] only when the [cd_trd_type] is not "I", otherwise it should give me"0".

When I add this expression I receive an error message that says:

"You tried to execute a query that doesn't include the specific expression
'IIF(Not[cd_trd_type]="I",[Year_Par],"0")' as part of an aggregate function"

After that I tried to put the above expression as:

Expand|Select|Wrap|Line Numbers
  1. (IIF(Not[cd_trd_type]="I",[Year_Par],"0")) 
It doesn't work either, and I get the same error message.

If anyone can tell me what I'm doing wrong, I'd appreciate it.

THANKS!!!

S
Jan 10 '08 #1
Share this Question
Share on Google+
2 Replies


P: 45
Did you type this up from scratch? Or did you use the query builder? I would paste the SQL string into a blank query and it should tell you where your error is. I think you are missing SUM.

I always have query builder build my SQL strings before moving it to my code page and cutting it up for variables.
Jan 11 '08 #2

P: 5
Did you type this up from scratch? Or did you use the query builder? I would paste the SQL string into a blank query and it should tell you where your error is. I think you are missing SUM.

I always have query builder build my SQL strings before moving it to my code page and cutting it up for variables.
I used the builder, but typed some of the strings myself. I will try what you suggest.
Thanks!
Jan 11 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.