If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
#Error on form
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! |
#2
|
|||
|
|||
#Error on form
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 "David" wrote: 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! |
#3
|
|||
|
|||
#Error on form
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? "Ofer" wrote: 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 "David" wrote: 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! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Search facility | tezza | General Discussion | 13 | February 18th, 2010 12:26 AM |
Move feild entries from form to form using global variables | JackCGW | General Discussion | 11 | November 14th, 2005 05:22 AM |
Tell if Form is a Dialog | Alex | Using Forms | 7 | August 30th, 2005 06:22 PM |
Need Help In Printing Current Record in Specific Report | RNUSZ@OKDPS | Setting Up & Running Reports | 1 | May 16th, 2005 09:06 PM |
auto entry into second table after update | Tony | New Users | 13 | July 9th, 2004 10:42 PM |