Jan 11 2009, 05:24 AM
I have downloaded a perfect system that allows me to store all of my training records, prices customer info etc and it even produced invoices, however I need to adapt the invoice to a TAX INVOICE, being that there must be a field that calculates the GST on that invoice.
So what i am thinking is that I need to just get a calucaltion to find out 10% of the total costs, (this being the GST) how do I do this
I have a field on the report Total Billables
I think that this fields calculations are done in a query, can i then do a calculation on a calcualton. I have very limited experience with access sorry guys
Jan 11 2009, 06:12 AM
From: Upstate NY, USA
You can use another controls value in the recordsource
of a control so try this:
Place a textbox control under the TotalsBillable control.
Name it GST (or whatever you want)
Set it's controlsource property to =[TotalsBillable] * .10
Place another textbox control below that.
Set it's controlsource property to = [TotalsBillable] + [GST]
Jan 11 2009, 07:13 AM
UA Forum + Wiki Administrator
From: Sudbury, Ontario, Canada
Ace's method for doing the GST calculation is correct.
However, you should keep in mind that tax rates can and to change from time to time. In Canada our GST rate dropped from 7% to 6% in 2006 and from 6% to 5% in 2008. So you may find it preferable not to hard code the tax rate into a query or report control and maintain a table of tax rate with their effective dates instead. Otherwise, if the rate does change you will not be able to reprint invoices for periods prior to the rate change and have the tax calculated correctly
My tax rate table consists of three fields in addition to a primary key:
effective date: dtmDateEffective this is the date on which the rate comes into effect
tax rate: currRate the rate expressed as a fraction
tax description : txtTaxType the common or officical name of the tax
The last field allows me to maintain several tax rates that might come into play. My business is subject only to GST but different types of businesses in Canada must also collect Provincial Sales Tax. In some other countries there are several levels of Sales tax. I wanted my method to be as flexible as possible.
In my tax calcuations I use a user defined function to retrieve the rate for the tax that is in effect on the invoice date:
' Procedure : fgetTaxRate
' DateTime : 07/07/2006 16:02
' Author : Glenn Lloyd
' Purpose : looks up the applicable rate in effect on the date specified
' relevant table structure:
' idsRatePK |(autonumber primary key)
' dtmDateEffective | date when a rate becomes effective
' strRateType | allows table to store multiple rate structures
Public Function fGetTaxRate(dtmDate As Date, strTable As String, strRateType) As Currency
Dim cResult As Currency
Dim strDateCrit As String
Dim dtmMin As Date
Dim dtmMax As Date
Dim strSQL As String
Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection
'find the limits of dates that apply to particular rate
On Error GoTo fGetTaxRate_Error
dtmMin = DMin("dtmDateEffective", strTable, "txtRateType = '" & strRateType & "'")
dtmMax = DMax("dtmDateEffective", strTable, "txtRateType = '" & strRateType & "'")
strDateCrit = fDateStr(DMax("dtmDateEffective", strTable, "txtRateType = '" & strRateType & "'"))
'eliminate the easy ones
If dtmDate < dtmMin Then
'assume the rate was zero until the earliest table entry for the type
cResult = 0
'assume the rate has not changed since the latest table entry for the type
If dtmDate >= dtmMax Then
'rate comes into effect on the specified date check for it or later
cResult = DLookup("currRate", strTable, "dtmDateEffective = " & strDateCrit)
'the record we need lies somewhere between earliest and latest date -
'process a recordset for the intervening records
strSQL = "SELECT * FROM " & strTable _
& " WHERE (dtmDateEffective >= " & fDateStr(dtmMin) _
& " AND txtRateType = '" & strRateType & "')" _
& " ORDER by dtmDateEffective;"
Set rst = New ADODB.Recordset
Set cnn = CurrentProject.Connection
rst.Open strTable, cnn, adOpenForwardOnly, adLockOptimistic
Do While Not .EOF
cResult = rst!currRate
If rst!dtmDateEffective > dtmDate Then
Set rst = Nothing
Set cnn = Nothing
fGetTaxRate = cResult
On Error GoTo 0
Select Case err.Number
MsgBox "Error " & err.Number & " (" & err.Description & ") " _
& "in procedure fGetTaxRate of Module basTaxRates"
You can place this code either in a standard VBA module or in the report's code module.
Jan 12 2009, 01:15 AM
I deal with about 100 different tax rates which are stored in a customer table. I calculate a total for the invoice based upon the sales tax rate, then print the tax as the difference between the total and all of the other entries (freight, non -taxed items, taxed items, labor & so on.) (This is the way it's calculated on our state's sales tax form.) If the sales person goofs an invoice (usually a customer satisfaction adjustment.) the sales tax will be way off and act as an audit tool to find invoices in need of review.
When I do the monthly summaries, i can then compare the "Calculated on the invoices" sum of sales tax to the Calculated from the sum of the invoices number. If the sales people have made any errors (there are always a couple each month) we can correct the errors & avoid (generally) overpayment of the tax. Tax audits can be a real pain, even when you're right.
|Go to Top · Lo-Fi Version||Time is now: 20th May 2013 - 04:29 AM|