Iff Statements

[JezLisle via AccessMonster.com]

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])'

 

[Klatuu]

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.

 

[JezLisle via AccessMonster.com]

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])

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.

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])'

 

[Klatuu]

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


--
Dave Hargis, Microsoft Access MVP

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])

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.

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])'

 

[JezLisle via AccessMonster.com]

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

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

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])'

 

[John Spencer]

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
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

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

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])'

 

[JezLisle via AccessMonster.com]

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
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])'