 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / May 2008Iff Statements
I am struggling with iff Statements. In my query I am trying to deduct one column from another but if there is a blank in one field then input zero. This is the statement I have tried but doesnt work... NA4: iff(([4]-[5]=0,0),[4]-[5]) It brings back an error.... Syntax error (comma) in query expression 'iff(( [4]-[5]=0,0),[4]-[5])' NA4: iff(([4]-[5]=0,0),[4]-[5]) Should be: NA4: iff(([4]-[5]=0,0,[4]-[5]) The structure of an IIf is: IIf(TrueFalseCondtion, ReturnIfTrue, ReturnIfFalse) But I would code it like this: NA4: Nz([4],0)-Nz([5],0) I sure am glad I will not have to come behind you and work on this database. Names like [4] and [5] and [NA4] are meaningless. There is no way to even guess at what they might mean.  SignatureDave Hargis, Microsoft Access MVP > I am struggling with iff Statements. In my query I am trying to deduct one > column from another but if there is a blank in one field then input zero. [quoted text clipped - 5 lines] > It brings back an error.... Syntax error (comma) in query expression 'iff(( > [4]-[5]=0,0),[4]-[5])' THanks, will give that a go... These are simple names I have used on here, to make it easier to write down. I know what you mean about working with stupid field names. I have had that in the past :-) >NA4: iff(([4]-[5]=0,0),[4]-[5]) > [quoted text clipped - 15 lines] >> It brings back an error.... Syntax error (comma) in query expression 'iff(( >> [4]-[5]=0,0),[4]-[5])' Oh, good, I feel better now :) Brought back the old days of Basic. Names could only be 2 characters and the first character had to be a letter and the second a number. There were only two kinds of variables, Text and Numeric. The difference was Text variable names had a $ in front, so you got names like $A $A0 $A1 - Text A A0 A1 - Numbers SignatureDave Hargis, Microsoft Access MVP > THanks, will give that a go... > [quoted text clipped - 21 lines] > >> It brings back an error.... Syntax error (comma) in query expression 'iff(( > >> [4]-[5]=0,0),[4]-[5])' I have tried the NA4: Nz([4],0)-Nz([5],0) with in my query, and still I get an error message. Am I supposed to put them as an expression? Allthough it doesnt matter if do or dont as the same message appears. You tried to execute a query that does not include the specified expression 'Nz([1],0)-Nz([2],0)' as part of an aggregate function. I'm not sure what it means? >Oh, good, I feel better now :) > [quoted text clipped - 10 lines] >> >> It brings back an error.... Syntax error (comma) in query expression 'iff(( >> >> [4]-[5]=0,0),[4]-[5])' It means that you are executing a query that is considered to be an aggregate (totals) query. Normally that means that you have a Sum, Avg, Min, Max or similar function in the query. Probably you need to GROUP BY this expression. If you can't solve this, I suggest you post the SQL of the query. (Select VIEW: SQL from the menu and post the text of the query.) John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County > I have tried the NA4: Nz([4],0)-Nz([5],0) with in my query, and still I get > an error message. Am I supposed to put them as an expression? Allthough it [quoted text clipped - 19 lines] >>>>> It brings back an error.... Syntax error (comma) in query expression 'iff(( >>>>> [4]-[5]=0,0),[4]-[5])' Excellent. Thanks that worked :-) >It means that you are executing a query that is considered to be an aggregate >(totals) query. Normally that means that you have a Sum, Avg, Min, Max or [quoted text clipped - 13 lines] >>>>>> It brings back an error.... Syntax error (comma) in query expression 'iff(( >>>>>> [4]-[5]=0,0),[4]-[5])' |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.