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.

 

[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