IFF Statement

[SAVLO]

I'm working with the query builder. The true part of my iff statement works
but for the false part I get an error. I want the query to return 0 if
expensed +expensed to date = AnnualTag Amount if not I want to the field to
populate with the Monthly amount.

MonthlyExpense: IIf([Expensed]+[ExpensedtoDate]=[Annual Tag
Amount],"0",[MonthlyAmount])

Thanks

 

[Golfinray]

Try
IIf([tablename.fieldname]+[tablename.fieldname]=[tablename.fieldname],"0",[Monthlyamount]) If you just put the field names it won't work.

 

[SAVLO]

All fields except the "Annual Tag Amount" are results from expressions built
within the same query. I tried entering the the tablename for the "Annual
Tag Amount" but I still get "Error".

Try
IIf([tablename.fieldname]+[tablename.fieldname]=[tablename.fieldname],"0",[Monthlyamount]) If you just put the field names it won't work.

I'm working with the query builder. The true part of my iff statement works
but for the false part I get an error. I want the query to return 0 if
expensed +expensed to date = AnnualTag Amount if not I want to the field to
populate with the Monthly amount.

MonthlyExpense: IIf([Expensed]+[ExpensedtoDate]=[Annual Tag
Amount],"0",[MonthlyAmount])

Thanks

 

[Golfinray]

Try an IIf(sum([expression+expression]...... - look up sums in access help

All fields except the "Annual Tag Amount" are results from expressions built
within the same query. I tried entering the the tablename for the "Annual
Tag Amount" but I still get "Error".

Try
IIf([tablename.fieldname]+[tablename.fieldname]=[tablename.fieldname],"0",[Monthlyamount]) If you just put the field names it won't work.

I'm working with the query builder. The true part of my iff statement works
but for the false part I get an error. I want the query to return 0 if
expensed +expensed to date = AnnualTag Amount if not I want to the field to
populate with the Monthly amount.

MonthlyExpense: IIf([Expensed]+[ExpensedtoDate]=[Annual Tag
Amount],"0",[MonthlyAmount])

Thanks

 

[SAVLO]

I have tried creating a query from the first query but I still get the same
"#Error". Any other suggestions? It will only answer the true part of the
IIf statement.
Thanks for your help.

 

[Clif McIrvin]

You don't say what error you're getting. Is [MonthlyAmount] in the same
table as the other fields? If not, you need to tell Access what table to
use:
[OtherTableName]!MonthlyAmount] ... will work *if* the query has related the
two tables together.

--
Clif
Still learning Access 2003

I have tried creating a query from the first query but I still get the same
"#Error". Any other suggestions? It will only answer the true part of
the
IIf statement.
Thanks for your help.

I'm working with the query builder. The true part of my iff statement
works
but for the false part I get an error. I want the query to return 0 if
expensed +expensed to date = AnnualTag Amount if not I want to the field
to
populate with the Monthly amount.

MonthlyExpense: IIf([Expensed]+[ExpensedtoDate]=[Annual Tag
Amount],"0",[MonthlyAmount])

Thanks

 

[Clif McIrvin]

I should have known better than to attempt a reply before looking for the
rest of the thread

So ... do I understand that [MonthlyAmount] is also a calculated field?

You say that you made two queries out of the one you were trying to make?
How? The first query should construct all the "simple" calculated fields,
and the second query any "nested" calculations, such as your IIF.

To say it another way: leave your original query as you had it, except
remove the calculated field

MonthlyExpense: IIf([Expensed]+[ExpensedtoDate]=[Annual Tag
Amount],"0",[MonthlyAmount])

Now create a second query which reads your first query (not your tables) and
insert your Monthly Expense calculation.

--
Clif
Still learning Access 2003

You don't say what error you're getting. Is [MonthlyAmount] in the same
table as the other fields? If not, you need to tell Access what table to
use:
[OtherTableName]!MonthlyAmount] ... will work *if* the query has related
the two tables together.

--
Clif
Still learning Access 2003

I have tried creating a query from the first query but I still get the
same
"#Error". Any other suggestions? It will only answer the true part of
the
IIf statement.
Thanks for your help.

I'm working with the query builder. The true part of my iff statement
works
but for the false part I get an error. I want the query to return 0 if
expensed +expensed to date = AnnualTag Amount if not I want to the field
to
populate with the Monthly amount.

MonthlyExpense: IIf([Expensed]+[ExpensedtoDate]=[Annual Tag
Amount],"0",[MonthlyAmount])

Thanks