Does someone know the formula for this calculation?

[USAussie]

Hi Everyone,
Since I had help here previously I'm hoping someone will be able to help me with a formula I'm stuck on.

I have create a report which will be an invoice to print out. On the report I am trying to get a field to have a correct formula to automatically calculate the sales tax on a purchase.

There are two possible calculations for this entry. If the Yes/No box called [Out of State Deal] is ticked then the Sales Tax figure needs to be $0.00. However, if the box is not ticked then the figure will be the result of this calculation [Sales Price]+[Tow Equipment Price]-[Trade In Allowance]*0.03.

***EDIT: Just to make things more difficult If the item purchased is a Park Model home them the sales tax will be $300. So I guess there's three different options here. I might have to abandon the idea of making this an auto-calculated field...

I will probably need to use an IIf function formula but everything I've tried is not working. Can anyone help? Hope the above is understandable.
Cheers,
Lis

 

[plog]

When you start to get this many conditions its time to make a function inside a module. What you do is set the control source on your report to this:

=getTaxAmount([Out of State Deal], [Sales Price], [Tow Equipment Price], [Trade In Allowance], [Item Purchased])

Then you create a module and paste this code in:

Function getTaxAmount(OutOfState, SalesPrice, TowEquipmentPrice, TradeAllowance, Item)
    ' determines tax rate on item based on where purchased, item purchased and prices
 
    ret = SalesPrice + TowEquipmentPrice - (TradeAllowance*0.03)
    ' sets default tax amount
 
    If OutOfState then  ret=0
    ' if out of state, no tax
 
    If Item="Park Model home" then ret=300
    ' if buying Park Model home, tax is 300
 
    getTaxAmount=ret
    ' returns tax rate
 
    End Function

Save and then run your report.

 

[USAussie]

Thanks so very much for your help! I will try this when I get a moment today and see how it goes!

 

[USAussie]

Hmm ok not sure what I'm doing wrong but when I go to print preview or report view etc it comes up with a pop-up box saying enter parameter value for getTaxAmount.
Any ideas what I might have done there?
Cheers,
Lis

 

[plog]

What exactly did you put in your control box on the report? My guess is you forgot the preceding equals sign.

 

[USAussie]

this is what I have in the control box

=getTaxAmount([Out of State Deal],[Sales Price],[Tow Equipment Price],[Trade In Allowance],[Unit Type])

And this is what I have in the "build event" code builder

Option Compare Database
Function getTaxAmount(OutOfState, SalesPrice, TowEquipmentPrice, TradeAllowance, UnitType)
' determines tax rate on item based on where purchased, item purchased and prices

ret = SalesPrice + TowEquipmentPrice - (TradeAllowance * 0.03)
' sets default tax amount

If OutOfState Then ret = 0
' if out of state, no tax

If UnitType = "Park Model" Then ret = 300
' if buying Park Model home, tax is 300

getTaxAmount = ret
' returns tax rate to
End Function

Private Sub Sales_Tax_BeforeUpdate(Cancel As Integer)

End Sub

 

[plog]

Are all of these fields in the underlying data source (Table/Query) of your report?

[Out of State Deal]
[Sales Price]
[Tow Equipment Price]
[Trade In Allowance]
[Unit Type]

 

[USAussie]

Yes that's the data field names. Unit type will tell whether it's a park model or other type of camper

 

[plog]

Can you post your database? Or I can give you an email if you would like to just send it to me.

 

[USAussie]

If you give me an email I'll send it to you sure. I appreciate the help.

 

[plog]

I don't get any errors when I open your report. Also, I don't see any modules in it.

 

[Pat Hartman]

[Sales Price]+[Tow Equipment Price]-[Trade In Allowance]*0.03

So far, no one has noticed the errors in this calculation.
1. If any field is null, the result will be null.
2. the tax rate is being applied only to the TradeInAllowance rather than to the entire sum. Add parentheses to control the order of operation when writing formulas that include +- with */. ([Sales Price]+[Tow Equipment Price]-[Trade In Allowance])*0.03

 

[Simon_MT]

What you could do create a field SalesTaxRef with a foreign Table SalesTax and store the rate of Sales Tax with any descriptions then:

([Sales Price]+[Tow Equipment Price]-[Trade In Allowance]) * SaleTaxRate

Use the data to do the work.

Simon

 

[USAussie]

That foreign table stuff is a going over my head. I'm not that advanced in formula knowledge.
But I'll see if I can figure it out