My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 Tax Invoice
|
|
 Jan 11 2009, 05:24 AM
Post
#1
|
|
|
New Member Posts: 1  |
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
Post
#2
|
|
|
UtterAccess VIP Posts: 5,278 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
Post
#3
|
|
|
UA Forum + Wiki Administrator Posts: 11,947 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: CODE '---------------------------------------------------------------------------------------
' 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 Else '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 Else 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 With rst Do While Not .EOF cResult = rst!currRate .MoveNext If rst!dtmDateEffective > dtmDate Then Exit Do End If Loop .Close End With End If End If Set rst = Nothing Set cnn = Nothing fGetTaxRate = cResult fGetTaxRate_Exit: On Error GoTo 0 Exit Function fGetTaxRate_Error: Select Case err.Number Case Else MsgBox "Error " & err.Number & " (" & err.Description & ") " _ & "in procedure fGetTaxRate of Module basTaxRates" End Select End Function You can place this code either in a standard VBA module or in the report's code module. Glenn |
|
|
|
|
Jan 12 2009, 01:15 AM
Post
#4
|
|
|
UtterAccess Addict Posts: 296 From: AZ |
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. |
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 20th May 2013 - 04:29 AM |