Add Order Total, Plus Freight, then calculate Sales Tax

[Carla Karstens via AccessMonster.com]

Hi,
I have spent the last few days trying to get my access program to do what I
need it to do. I am trying to enter customer orders so I can print
invoices. I need to have the invoice calculate their total, then add their
freight cost, then apply sales tax based on that subtotal. This is the
formula that was in there:

=CLng([Order Subtotal]*[SalesTaxRate]*100)/100

I tried to change it by doing this:

=CLng([Order Subtotal]+[FreightCharge]*[SalesTaxRate]*100)/100
But that did not work...
Can anyone tell me how to do that?

 

[Ed Warren]

Example calculation

Order $100.00
Freight $10.00
TaxRate 10% -- taxrate stored as (0.10)

Desired Result:

$100.00 + $10.00 + ($110.00)*(.10) = $121.00

so either

ordersubtotal + Freight + ((ordersubtotal+freight)*taxrate)
or

(ordersubtotal+Freight)(1+taxrate)

if [taxrate] stored as 10 then must divide by 100 to get 0.10

ordersubtotal+freight+((ordersubtotal+freight)*tax/100)
or
(ordersubtotal+freight)(1+(taxrate/100))

Ed Warren

 

[PC Datasheet]

I don't know if this is what you are asking but you calculate the subtotal
in the page footer of the orderdetails subform and then carry it over to the
orders form with something like:
Me!OrderSubtotal = Me!SFrmOrderDetails!Subtotal

 

[Carla Karstens via AccessMonster.com]

Thank you for taking the time to try to help me!! I tried using your
example, but this still is not working, and I am thinking it is because I
am not getting all the [] and () in the right places.
I am using a premade database in access called order entry.
And the formula that was set in there is:

=CLng([Order Subtotal]*[SalesTaxRate]*100)/100

This would be fine, but it does not include the shipping before it
calculates the tax. I don't understand how to add the other field, called
FreightCharge into the above formula.

Thanks again....I appreciate it very much!

 

[Ed Warren]

Take a close look at the formula you have provided below.

Assume your order is $100.00 and taxrate = 10%

The result of the calculation provided would be

$100*0.10*100/100 = $10.00

is would be correct for the amount of tax to charge, but not for the total
amount of the sale.

?? is this the result you expect/want??

If not, one might, just might question the formula, no matter it's pedigree.

Ed Warren

 

[George Nicholson]

But that did not work...

Um, *how* did it not work? No result? "Wrong" or unexpected result? #Error?
....?

=CLng([Order Subtotal]+[FreightCharge]*[SalesTaxRate]*100)/100

Multiplication takes precedence over addition. If you want OrderSubtotal and
FreightCharge added together before multiplied by SalesTaxRate they should
be enclosed in paranthesis, otherwise OrderSubtotal will be added to the
result of FreightCharge*SalesTaxRate*100 (the integer value of which is then
divided by 100), which I am sure would be viewed as an unexpected/wrong
result.

=CLng(([Order Subtotal]+[FreightCharge])*[SalesTaxRate]*100)/100


--
George Nicholson

Remove 'Junk' from return address.

Hi,
I have spent the last few days trying to get my access program to do what
I
need it to do. I am trying to enter customer orders so I can print
invoices. I need to have the invoice calculate their total, then add their
freight cost, then apply sales tax based on that subtotal. This is the
formula that was in there:

=CLng([Order Subtotal]*[SalesTaxRate]*100)/100

I tried to change it by doing this:

=CLng([Order Subtotal]+[FreightCharge]*[SalesTaxRate]*100)/100
But that did not work...
Can anyone tell me how to do that?

 

[Carla Karstens via AccessMonster.com]

Now that worked perfectly!!
THANK YOU....THANK YOU...THANK YOU!