IFF Statement


S

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
 
Ad

Advertisements

G

Golfinray

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

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

Golfinray said:
Try
IIf([tablename.fieldname]+[tablename.fieldname]=[tablename.fieldname],"0",[Monthlyamount]) If you just put the field names it won't work.
SAVLO said:
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
 
G

Golfinray

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

SAVLO said:
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".

Golfinray said:
Try
IIf([tablename.fieldname]+[tablename.fieldname]=[tablename.fieldname],"0",[Monthlyamount]) If you just put the field names it won't work.
SAVLO said:
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
 
S

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

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



SAVLO said:
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.

SAVLO said:
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
 
Ad

Advertisements

C

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



Clif McIrvin said:
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



SAVLO said:
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.

SAVLO said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top