Calculations

  • Thread starter Thread starter tinab
  • Start date Start date
T

tinab

I have a report with a CategoryID header and footer. In the detail
section I have the ProductID field, Quantity field and a Price field
with the calculation Quantity * Unit PRice. In the CategoryID footer
I have a text box for a subtotal of that category
=Sum([Quantity]*[UnitPrice]) and one called GSTTax
=Sum([Quantity]*[UnitPrice])*[GST] and one called PSTTax
=Sum([Quantity]*[UnitPrice])*[PST]. In the report footer I want to
create a field to show total PSTTotal and total GSTTotal for this
report. I used the formula =Sum([Quantity]*[UnitPrice])*[GST] and it
works for the GST but not PST because some categories have a $0.00
PSTTax. Thanks, Tina
 
Are you certain that the underlying field(s) holds a "0", and not a Null?
Have you tried using the Nz() function to convert Null to 0?
 
Try
=Sum([Quantity]*[UnitPrice])
=Sum([Quantity]*[UnitPrice]*[GST])
=Sum([Quantity]*[UnitPrice]*[PST])

--
Duane Hookom
MS Access MVP
--

Jeff Boyce said:
Are you certain that the underlying field(s) holds a "0", and not a Null?
Have you tried using the Nz() function to convert Null to 0?

--
Good luck

Jeff Boyce
<Access MVP>

tinab said:
I have a report with a CategoryID header and footer. In the detail
section I have the ProductID field, Quantity field and a Price field
with the calculation Quantity * Unit PRice. In the CategoryID footer
I have a text box for a subtotal of that category
=Sum([Quantity]*[UnitPrice]) and one called GSTTax
=Sum([Quantity]*[UnitPrice])*[GST] and one called PSTTax
=Sum([Quantity]*[UnitPrice])*[PST]. In the report footer I want to
create a field to show total PSTTotal and total GSTTotal for this
report. I used the formula =Sum([Quantity]*[UnitPrice])*[GST] and it
works for the GST but not PST because some categories have a $0.00
PSTTax. Thanks, Tina
Click to expand...
Click to expand...
 
I dont' know how to do that. Pleas help and I thank you so much.

Jeff Boyce said:
Are you certain that the underlying field(s) holds a "0", and not a Null?
Have you tried using the Nz() function to convert Null to 0?

--
Good luck

Jeff Boyce
<Access MVP>

tinab said:
I have a report with a CategoryID header and footer. In the detail
section I have the ProductID field, Quantity field and a Price field
with the calculation Quantity * Unit PRice. In the CategoryID footer
I have a text box for a subtotal of that category
=Sum([Quantity]*[UnitPrice]) and one called GSTTax
=Sum([Quantity]*[UnitPrice])*[GST] and one called PSTTax
=Sum([Quantity]*[UnitPrice])*[PST]. In the report footer I want to
create a field to show total PSTTotal and total GSTTotal for this
report. I used the formula =Sum([Quantity]*[UnitPrice])*[GST] and it
works for the GST but not PST because some categories have a $0.00
PSTTax. Thanks, Tina
Click to expand...
Click to expand...
 
See Duane's suggestion, and take a look at Access HELP on NullToZero
function (or Nz function). Any field that could hold a Null could cause
your calculation to return a Null overall.

--
Good luck

Jeff Boyce
<Access MVP>

tinab said:
I dont' know how to do that. Pleas help and I thank you so much.

"Jeff Boyce" <[email protected]_HYPHEN_TO_END> wrote in message
Are you certain that the underlying field(s) holds a "0", and not a Null?
Have you tried using the Nz() function to convert Null to 0?

--
Good luck

Jeff Boyce
<Access MVP>

tinab said:
I have a report with a CategoryID header and footer. In the detail
section I have the ProductID field, Quantity field and a Price field
with the calculation Quantity * Unit PRice. In the CategoryID footer
I have a text box for a subtotal of that category
=Sum([Quantity]*[UnitPrice]) and one called GSTTax
=Sum([Quantity]*[UnitPrice])*[GST] and one called PSTTax
=Sum([Quantity]*[UnitPrice])*[PST]. In the report footer I want to
create a field to show total PSTTotal and total GSTTotal for this
report. I used the formula =Sum([Quantity]*[UnitPrice])*[GST] and it
works for the GST but not PST because some categories have a $0.00
PSTTax. Thanks, Tina
Click to expand...
Click to expand...
Click to expand...
 
Back
Top