Multiple Formula Issues

[colbywolford]

Hi, all,

I have my own editing business. I've tried numerous accounting packages and haven't been thrilled with any of them because I don't have much accounting knowledge and they all seem to be overly complicated. Anyway, I decided to create my own accounting program with Access 2013. I have successfully created client and vendor forms with contact subforms. My problems exist on my sad attempts at creating an order form with a subform that tracks line items from the order. The order form contains the client information along with the order number and the order date. The subform contains all the specific line items for the order.

The first problem I'm having deals with checkboxes. I've checked the forums, but I can't find a solution that fits my needs or I can't understand the answer. I have two checkboxes, one named Rush and one named Taxed. For Rush, I need to count the number of checked checkboxes and multiply the total by 10 to give me the total rush charges. For Taxed, I have this worked with a field called Line Tax. The Line Tax is calculated based on the Line Total and whether or not the Taxed checkbox is checked. No matter how I manipulate the formulas, neither one is working. Rush never tallies and LineTax is always figured whether or not the Taxed is checked.

Right now I have the following...
Rush: =Count(IIF([Rush]=true,1,0))
Taxed: =IIF([Taxed]=true,[LineTotal]*0.06,0)

My other problem stems from trying to get a Subtotal and Total Tax fields to work. =SUM([LineTotal]) and =SUM([LineTax]) give me errors.

What am I doing wrong? I'll admit, most of my problem is that I'm trying to use Excel logic with Access (and I know that doesn't work). But at least I'm trying!

[davegri]

It would really, really help to see a screen shot of your form with the subform so that we can tell where all the checkboxes and textboxes are. Also, in the code you show, where is it - in a query or in textboxes?

[colbywolford]

Here's the screenshot. I guess this will take a little more explanation. Each of the formulas is listed in the text boxes in the lower left corner of the subform. Basically what I did was set up formula boxes and then set the Line Tax, Subtotal, Total Tax, and Rush Fees equal to their corresponding formula boxes since you can't perform functions on calculated fields.

So here are the formulas in each of the formula fields:
LineTotalFormula "=[Qty]*[FeeAmount]"
LineTaxTotal "=Iif([Taxable]=true,[LineTotal]*0.06,0)"
RushCount "=Count(Iif([Rush]=true,1,0)"
RushFormula "=[RushCount]*10"
SubtotalFormula "=Sum([LineTotal])"
TotalTaxFormula "=Sum([LineTax])"
TotalFormula "=Sum([Subtotal]+[TotalTax]+[RushFee])"

[davegri]

Your formulas are getting errors because they have no data to work with.
Also, the way you are set up, the calculated fields will only reflect the values of one record in the subform, not the aggregated value of all the items.

[colbywolford]

How do you mean they have no data to work with? I get that they don't have any values to work with on a blank record, but how do they not have values to work with when I do input info into the fields? And how do I go about fixing these errors?

[davegri]

Well, you showed me a form with errors and I told you why. It did so because there's no data.
Take a look at the Northwind database that Microsoft has provided for many years as an Access example. Its focus is on order processing, a bit similar to yours. It will show you how to combine a main form with a subform and get interactive totals between them.

[colbywolford]

I've tried looking at the Northwind database, but as a novice user, I get lost in all the forms and tables and such. That was actually the first thing I did before beginning this post.