#Error on form

[David]

I have hit a wall in my thinking – just can not figure out why I am getting
an #Error on my subtotal box.
I have an Invoice form. Recent tax changes required me to add the ysnTax
box to check if the item was taxable or not taxable. Previously, I had been
just selecting whether or not to add tax to the entire order on the Invoice
footer with a drop-down box. The Taxable/NonTaxable IIf statements seem to
work, however the text boxes I have to subtotal all the items with a check
and all the items without checks is giving me an error.
I read other posts about this, but they do not hold the answer for my problem.
I need to keep track of the tax separately for the Sales Tax report.
Here is what I have:
frmInvoice w/frmInvoiceDetailsSubform
On the subform, based on Select (SQL) Query with fields lngItemNumber,
strDescription, dblQuanity, curPricePerUnit, ysnTax which all pull from the
Inventory Table and Invoice Details table and an Ext Price calculation in the
SQL query that takes the Qty * Price for each line. I added 2 new text boxes
to the form footer here which are Named Taxable and has the control source of
=IIf ([ysnTax]=True,[Ext Price],0) and NTaxable with the control source of
=IIf ([ysnTax]=False,[Ext Price],0) These show up and work correctly when I
check the box or do not check it. Next, I wanted to sum all extended prices
with checks and the extended prices without checks in separate text boxes on
the frmInvoiceDetailsSubform Footer. I added two more text boxes to the
frmInvoiceDetailsSubform footer named SumTax with control source of
=Sum[Taxable] and SumNTax with control source of =Sum[NTaxable]. The last 2
are the ones that come up with an #Error that I can’t seem to figure out.
I hope this is enough information.
Any help/advice from you who are wiser would be appreciated!

 

[Ofer]

Try this

=Sum(IIf ([ysnTax]=True,[Ext Price],0))

The sum function work with the table field name (Record source of the form),
and not with a name of a calculated field in the form, and this is why you
should use the iif in the sum.

Also, if the you need to do sum on [Ext Price]. you would write
=Sum([Ext Price])

And not
=Sum[Ext Price]

 

[David]

Since you did not specify where, I tried it in Taxable, which did not work
and then in SumTax. Both times I still got the #Error. Any other ideas?

Try this

=Sum(IIf ([ysnTax]=True,[Ext Price],0))

The sum function work with the table field name (Record source of the form),
and not with a name of a calculated field in the form, and this is why you
should use the iif in the sum.

Also, if the you need to do sum on [Ext Price]. you would write
=Sum([Ext Price])

And not
=Sum[Ext Price]

--
\\// Live Long and Prosper \\//
BS"D

I have hit a wall in my thinking – just can not figure out why I am getting
an #Error on my subtotal box.
I have an Invoice form. Recent tax changes required me to add the ysnTax
box to check if the item was taxable or not taxable. Previously, I had been
just selecting whether or not to add tax to the entire order on the Invoice
footer with a drop-down box. The Taxable/NonTaxable IIf statements seem to
work, however the text boxes I have to subtotal all the items with a check
and all the items without checks is giving me an error.
I read other posts about this, but they do not hold the answer for my problem.
I need to keep track of the tax separately for the Sales Tax report.
Here is what I have:
frmInvoice w/frmInvoiceDetailsSubform
On the subform, based on Select (SQL) Query with fields lngItemNumber,
strDescription, dblQuanity, curPricePerUnit, ysnTax which all pull from the
Inventory Table and Invoice Details table and an Ext Price calculation in the
SQL query that takes the Qty * Price for each line. I added 2 new text boxes
to the form footer here which are Named Taxable and has the control source of
=IIf ([ysnTax]=True,[Ext Price],0) and NTaxable with the control source of
=IIf ([ysnTax]=False,[Ext Price],0) These show up and work correctly when I
check the box or do not check it. Next, I wanted to sum all extended prices
with checks and the extended prices without checks in separate text boxes on
the frmInvoiceDetailsSubform Footer. I added two more text boxes to the
frmInvoiceDetailsSubform footer named SumTax with control source of
=Sum[Taxable] and SumNTax with control source of =Sum[NTaxable]. The last 2
are the ones that come up with an #Error that I can’t seem to figure out.
I hope this is enough information.
Any help/advice from you who are wiser would be appreciated!