subtracting one IIF from another

[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

 

[Terry]

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

 

[Bill]

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


.

 

[Marshall Barton]

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?

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.

 

[Terry]

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

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]

Hi Marsh,
Thanks for your assistance, worked well as soon as I got the
,0
in the correct positions in the syntax.
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].[ContactI

D

])=1,([MonthlyVisits]*[CurrRateAmt]))

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

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.