How to limit =SUM to False based on Check Box

  • Thread starter Thread starter Scott B
  • Start date Start date
S

Scott B

Good Morning,

I am trying to create a calculated field in a report that creates a sales
tax report (driven by a query). I have to report tax exempt sales, but I do
not include them in the taxable gross sales for sales tax purposes. So
something like =SUM([TotalSales]) (TotalSales is a calculated field from a
query) will not work. I need to do something like: =Sum([TaxExempt] for
[TaxExempt] = False to get only the taxable sales. I am currently trying
to use DSUM. Here is my ltest iteration which is returning #ERROR:
=DSum("[RoomTotal]","[qrySalesTax]","[TaxExempt] = 'No' ")
[RoomTotal] = Calc. Control on query. [qrySalesTax] = Query with Calc.
control. [TaxExempt] = check box from Query: qrySalesTax.

I would be very grateful if someone could guide me to the mistake in my
syntax or my choice of a function.

Thanks in advance,
Scott B
 
TaxExempt is a check box so it will not give a text value of 'No' or 'Yes'
Try this instead
=DSum("[RoomTotal]","[qrySalesTax]","[TaxExempt] = False")
 
Dennis,

Thanks you for the quick reply. I put your suggestion in the field line of
the query and it works, but I hade to disable the query criteria to get it
to work. I still get a criteia box because I have the criteria repaeated on
the form so I will have a reference of what dates I asked for.

With the control working I get the correct data on the form, but I cannot
format the control. I need Currency, but I get no selections in the Format
drop down and typing in Currency does not have any effect. I changed the
calculated field format in the query, but it had no effect either. I really
need to make the query version work, because I need the calculated field to
make further calculations. Putting it on the form makes it impossible (in
my limited expereince to make a further calculations from that control.

Any thoughts?

Again.,thanks for the help.
Scott B

Dennis said:
TaxExempt is a check box so it will not give a text value of 'No' or 'Yes'
Try this instead
=DSum("[RoomTotal]","[qrySalesTax]","[TaxExempt] = False")

Scott B said:
Good Morning,

I am trying to create a calculated field in a report that creates a sales
tax report (driven by a query). I have to report tax exempt sales, but I
do
not include them in the taxable gross sales for sales tax purposes. So
something like =SUM([TotalSales]) (TotalSales is a calculated field from
a
query) will not work. I need to do something like: =Sum([TaxExempt] for
[TaxExempt] = False to get only the taxable sales. I am currently
trying
to use DSUM. Here is my ltest iteration which is returning #ERROR:
=DSum("[RoomTotal]","[qrySalesTax]","[TaxExempt] = 'No' ")
[RoomTotal] = Calc. Control on query. [qrySalesTax] = Query with Calc.
control. [TaxExempt] = check box from Query: qrySalesTax.

I would be very grateful if someone could guide me to the mistake in my
syntax or my choice of a function.

Thanks in advance,
Scott B
Click to expand...
Click to expand...
 
Maybe a little clearer. Everything works until I put a criteria in the
query. I need the criteria. The =DSUM is in the form. It works as long as
I do not try to limit the date scope.

Scott B


Dennis said:
TaxExempt is a check box so it will not give a text value of 'No' or 'Yes'
Try this instead
=DSum("[RoomTotal]","[qrySalesTax]","[TaxExempt] = False")

Scott B said:
Good Morning,

I am trying to create a calculated field in a report that creates a sales
tax report (driven by a query). I have to report tax exempt sales, but I
do
not include them in the taxable gross sales for sales tax purposes. So
something like =SUM([TotalSales]) (TotalSales is a calculated field from
a
query) will not work. I need to do something like: =Sum([TaxExempt] for
[TaxExempt] = False to get only the taxable sales. I am currently
trying
to use DSUM. Here is my ltest iteration which is returning #ERROR:
=DSum("[RoomTotal]","[qrySalesTax]","[TaxExempt] = 'No' ")
[RoomTotal] = Calc. Control on query. [qrySalesTax] = Query with Calc.
control. [TaxExempt] = check box from Query: qrySalesTax.

I would be very grateful if someone could guide me to the mistake in my
syntax or my choice of a function.

Thanks in advance,
Scott B
Click to expand...
Click to expand...
 
Back
Top