subtracting one IIF from another

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

Win98 SE, A2k
Need to get a total in the report footer that
reflects the total PT - the supervisors amount.


The following give the desired results in a query feeding the report.
PT creates the total PT dollars.
Supv creates the supv dollars.

PT: IIf(([TherID])=1,([MonthlyVisits]*[CurrRateAmt]))
Supv: IIf(([Contacts].[ContactID])=1,([MonthlyVisits]*[CurrRateAmt]))

However, when I attempt to subtract Supv from PT, I get nothing.

This is the syntax I am using:
(It is in the field block of the query grid)
TotalPT:
IIf(([TherID])=1,([MonthlyVisits]*[CurrRateAmt]))-IIf(([Contacts].[ContactID
])=1,([MonthlyVisits]*[CurrRateAmt]))

Can you subtract one IIF from another? If not, can you point me to an
alternative?

Thanks,
Bill
 
Bill,

create another query based on the query which creates the
PT and supv fields and in this new query perform you next
calculation. I have found (and this may be wrong) that
queries won't necessarily perform calculations in the
order you want so if you are calculating calculated
results it is safer to spread them over multiple queries.

HTH,

Terry
 
Terry,
Thanks for the reply, but no good.
I get the same result, Zeros.
Any other ideas?
Anyone else got a suggestion?

Thanks,
Bill
Terry said:
Bill,

create another query based on the query which creates the
PT and supv fields and in this new query perform you next
calculation. I have found (and this may be wrong) that
queries won't necessarily perform calculations in the
order you want so if you are calculating calculated
results it is safer to spread them over multiple queries.

HTH,

Terry
-----Original Message-----
Win98 SE, A2k
Need to get a total in the report footer that
reflects the total PT - the supervisors amount.


The following give the desired results in a query feeding the report.
PT creates the total PT dollars.
Supv creates the supv dollars.

PT: IIf(([TherID])=1,([MonthlyVisits]*[CurrRateAmt]))
Supv: IIf(([Contacts].[ContactID])=1,([MonthlyVisits]* [CurrRateAmt]))

However, when I attempt to subtract Supv from PT, I get nothing.

This is the syntax I am using:
(It is in the field block of the query grid)
TotalPT:
IIf(([TherID])=1,([MonthlyVisits]*[CurrRateAmt]))-IIf (([Contacts].[ContactID
])=1,([MonthlyVisits]*[CurrRateAmt]))

Can you subtract one IIF from another? If not, can you point me to an
alternative?

Thanks,
Bill


.
Click to expand...
Click to expand...
 
Bill said:
Win98 SE, A2k
Need to get a total in the report footer that
reflects the total PT - the supervisors amount.


The following give the desired results in a query feeding the report.
PT creates the total PT dollars.
Supv creates the supv dollars.

PT: IIf(([TherID])=1,([MonthlyVisits]*[CurrRateAmt]))
Supv: IIf(([Contacts].[ContactID])=1,([MonthlyVisits]*[CurrRateAmt]))

However, when I attempt to subtract Supv from PT, I get nothing.

This is the syntax I am using:
(It is in the field block of the query grid)
TotalPT:
IIf(([TherID])=1,([MonthlyVisits]*[CurrRateAmt]))-IIf(([Contacts].[ContactID
])=1,([MonthlyVisits]*[CurrRateAmt]))

Can you subtract one IIF from another? If not, can you point me to an
alternative?
Click to expand...


Sure you can do subtraction in a calculated query field. I
think what you're missing here is the value you want to use
when the TherID and ContactID are not 1. In a query (as
opposed to VBA), when you leave out the false part of an
IIf, it returns Null. Anything added, subtracted, etc. with
Null results in Null.
 
Bill,

I think Marshall might be on the right track with his
reply. Let us know how you get on ...

Terry
-----Original Message-----
Terry,
Thanks for the reply, but no good.
I get the same result, Zeros.
Any other ideas?
Anyone else got a suggestion?

Thanks,
Bill
Bill,

create another query based on the query which creates the
PT and supv fields and in this new query perform you next
calculation. I have found (and this may be wrong) that
queries won't necessarily perform calculations in the
order you want so if you are calculating calculated
results it is safer to spread them over multiple queries.

HTH,

Terry
-----Original Message-----
Win98 SE, A2k
Need to get a total in the report footer that
reflects the total PT - the supervisors amount.


The following give the desired results in a query
Click to expand...
feeding
the report.
PT creates the total PT dollars.
Supv creates the supv dollars.

PT: IIf(([TherID])=1,([MonthlyVisits]*[CurrRateAmt]))
Supv: IIf(([Contacts].[ContactID])=1,([MonthlyVisits]* [CurrRateAmt]))

However, when I attempt to subtract Supv from PT, I get nothing.

This is the syntax I am using:
(It is in the field block of the query grid)
TotalPT:
IIf(([TherID])=1,([MonthlyVisits]*[CurrRateAmt]))-IIf (([Contacts].[ContactID
])=1,([MonthlyVisits]*[CurrRateAmt]))

Can you subtract one IIF from another? If not, can you point me to an
alternative?

Thanks,
Bill


.
Click to expand...
Click to expand...


.
Click to expand...
 
Hi Marsh,
Thanks for your assistance, worked well as soon as I got the
,0
in the correct positions in the syntax.
Bill

Marshall Barton said:
Bill said:
Win98 SE, A2k
Need to get a total in the report footer that
reflects the total PT - the supervisors amount.


The following give the desired results in a query feeding the report.
PT creates the total PT dollars.
Supv creates the supv dollars.

PT: IIf(([TherID])=1,([MonthlyVisits]*[CurrRateAmt]))
Supv: IIf(([Contacts].[ContactID])=1,([MonthlyVisits]*[CurrRateAmt]))

However, when I attempt to subtract Supv from PT, I get nothing.

This is the syntax I am using:
(It is in the field block of the query grid)
TotalPT:
IIf(([TherID])=1,([MonthlyVisits]*[CurrRateAmt]))-IIf(([Contacts].[ContactI
Click to expand...
D
])=1,([MonthlyVisits]*[CurrRateAmt]))

Can you subtract one IIF from another? If not, can you point me to an
alternative?
Click to expand...


Sure you can do subtraction in a calculated query field. I
think what you're missing here is the value you want to use
when the TherID and ContactID are not 1. In a query (as
opposed to VBA), when you leave out the false part of an
IIf, it returns Null. Anything added, subtracted, etc. with
Null results in Null.
Click to expand...
 
Back
Top