Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Continuouse Form Issues (2003)

    I am working on a Point of Sale program, but am having some issues. I'm using a continuous form and need some totals for each item and also for the overall sale.

    Each item can be taxable or not. The tax rate is stored in a non-visible text field on the form.
    Each item can also be discounted, pre-tax.

    Fields A, B, & C are bound to a table.

    Field D should be A*B-(C/100*A*
    Field E should be Field D + TaxRate*Field D
    Field F should be the sum of A*B
    Field H should be the sum of the discoutns for each record.
    Field G should be the sum of the taxes for each record
    Field I should be the total due.

    I can figure Fields A-F, but not G, H, or I.

    Any pointers?

    Thanks!
    Attached Images Attached Images
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Continuouse Form Issues (2003)

    The control source for F should be

    =Sum([A]*<!t>[B])

    Thaf for G:

    =[TaxRate]*Sum([A]*<!t>[B]*(1-[C]/100))

    That for H:

    =Sum([A]*<!t>[B]*[C]/100)

    And for I:

    =Sum(([A]*<!t>[B]*(1-[C]/100))*(1+[TaxRate])

    Substitute the correct field names.

  3. #3
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Continuouse Form Issues (2003)

    Those formulas worked great, thanks.

    One thing that I need to include, however, is that each line may or may not be taxable. If I uncheck the Taxable checkbox, tax should not be calculated for that item.

    I added a text box for each record that calculates the tax for the item =IIf([chkTaxable]=True,[txtTaxRate]*[Quantity]*([Price]-([Price]*[Discount]/100)),0) and I hoped to have G be the sum of that text box, but all I get is #ERROR in all the Order Total boxes.

    Is there a way to make each item toggle the tax-ability?
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Continuouse Form Issues (2003)

    You should use the name of the taxable Yes/No field in the underlying table as chkTaxable (not the name of the check box if that is different from the name of the field).
    And you should use the sum of the expression in the footer, not the sum of the Tax text box: =Sum(IIf(...))

  5. #5
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Continuouse Form Issues (2003)

    Ok, I made the text box for the tax for each record use this:
    =IIf([Taxable]=True,[txtTaxRate]*[Quantity]*([Price]-([Price]*[Discount]/100)),0)

    and I made the footer tax box this:
    =Sum(IIf([Taxable]=True,[txtTaxRate]*[Quantity]*([Price]-([Price]*[Discount]/100)),0))

    I still get #ERROR

    I'm attaching the database so you can see what I am doing. If you test it out, you can use 1 as the ISBN and it will pull the price for you.
    Attached Files Attached Files
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Continuouse Form Issues (2003)

    In the attached version, I've moved the calculations to a query. The formulas in the footer now work.
    Attached Files Attached Files

  7. #7
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Continuouse Form Issues (2003)

    Thank you!

    I had to change the Total Due to =[txtsubtotal]+[txttax]-[txttotaldiscount]

    It was not excluding the tax otherwise.

    Thanks so much for the help!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •