using two fields

[Lin Light]

I have a query based on two tables - dept funds and the other is charges
against those dept funds. It is a simple query, used it in the past, but now
there is a catch.
I now have been asked to in a column for Revised fund amount. This column
is to reflect any changes to the orginal fund (Increase or decrease) In the
past I would just sum expenditures against a dept, subtract it from the fund,
take the balance and divide to get the % left. Now I have two columns one
with the beginning fund and another with Revised fund. Not all my funds are
revised. Question How do I get the program to selected the revised only when
it has a value greater than 0 for subtraction of expenditures?
Please make the it simple
Lin

 

[John Spencer (MVP)]

You'll need to use a calculated value and the IIF function. Perhaps something like:

Sum(IIF([Revised]=0,[Fund],[Revised])) - Sum([Expenditure])

 

[Lin Light]

ONCE again this group has make my life better.
And I should add, if I don't tell the folks for whom
this report is for that I got outside help, it makes
me look like I know what I'm doing.
Lin

You'll need to use a calculated value and the IIF function. Perhaps something like:

Sum(IIF([Revised]=0,[Fund],[Revised])) - Sum([Expenditure])

I have a query based on two tables - dept funds and the other is charges
against those dept funds. It is a simple query, used it in the past, but now
there is a catch.
I now have been asked to in a column for Revised fund amount. This column
is to reflect any changes to the orginal fund (Increase or decrease) In the
past I would just sum expenditures against a dept, subtract it from the fund,
take the balance and divide to get the % left. Now I have two columns one
with the beginning fund and another with Revised fund. Not all my funds are
revised. Question How do I get the program to selected the revised only when
it has a value greater than 0 for subtraction of expenditures?
Please make the it simple
Lin