URGENT!!!!!! Calculating VAT (sales TAX)

Hello, First I must say thank you for all the help I have received from this forum and the others.(Tables, etc.)

The new question I have involves calculating in reports. I have set up an parameter query to generate an invoice. I have done a report based on the parameter query and it works well. I have done it so it calculates a subtotal of all the jobs we have done for the customer (basically what they owe us Without VAT [sales tax})

Basically Now I need to add VAT (as it is called in England and Sales Tax in other parts of the world) The VAT in England is 17.5% and I have to add this on to the subtotal. Please I need help to do this. I know how to calculate the VAT on paper when I have been doing the Invoices manually. Can I use the same formula that I use to calculate manually on the computer or does it have to be a different way???

I have it written down somewhere and I will write the formula that i use on paper to get the VAT and then the Grand Total when I do the invoices manually and send it in and maybe someone can tell me if I am able to use the same formula to do the VAT on access??

In the meantime if anyone has the answer now don't hesitate to let me know ok as I need to sort this out ASAP. I did have a formula to try out however when I did try it out it gives me to many figures like 345.0987876 ya get me and I need to get it to look like money ya get me??

PLEASE PLEASE PLEASE PLEASE HELP ME!!! I gotta have this sorted by 1:00 in the afternoon tomorrow to start the invoices on access.

THANXX xxxxx!!!!!xxxxx

Post your answer or comment

comments powered by Disqus
I have a form on a database that consist of many fields, and part of it has 3 fields that are:
- Recovery Price [txtrecovery]
- Storage Price [txtstorage]
- Extra Items Price [txtextra]

All of these are NET prices and I have a VAT (or sales tax in the u.s) text box that performs a calculation to work out the 17.5% VAT value of all 3 fields combined.

The name of that txt box is - [txtvat] That has worked fine but...

In the UK out VAT (sales tax) is changing from 17.5% to 15%, and the way I've made the database require a slight code change. Now bear in mind the database may not be technically correct in that it has one main form for lots of things, but I'm happy with that.

Now, each record on the form is identified by a unique job number such as '9876'. I would like to know the code/calculation for the VAT box to perform a special calculation.

I want all records after lets say '8000' to calculate at 15% the 3 field (recovery, storage and extra) and all records below 8000 to calculate at 17.5% vat. I hope that makes sense.

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.

I'm working on an application that will send data (expense forms, populated with basic credit card transaction details - nothing too sensitive) out by email to a long list of people, they will populate some blank fields in the spreadsheet, then send it back - it will be imported back into the database, validated against a few calculated bits and pieces, then summarised and reported for journalling in the accounting system.

Trouble is... they want the spreadsheets to be more than just unadorned tables.

They want it to look like a proper form - so half a dozen rows at the top, containing details about the recipient, the billing period, etc. They also want it to include a formula that will automatically total and balance the entries made in the system against the total for the statement - the user-entered values break down the VAT (sales tax), so there is a need to make sure it balances.

Then when it comes back, I've got to get my application to pluck the data out of this grid - remembering it's not at the top of the sheet.

So... how, if at all, am I going to tackle this? I know A2007 has some options for preserving report formatting when exporting, but this one has to run in A2000. I've automated the export, import and emailing of files before, but I don't think this one is going to be like the ones I've done in the past.

Is their expectation realistic? How should I proceed?


I am new to building databases. I have created a materials table which as net cost, VAT rate, calculates total cost per unit etc.

I have a VAT rates table separately. The VAT rate field in the Materials table is supposed to have a look up to the VAT rate table (which it does).

However, when I calculate VAT amount in the Materials table (net cost field x VAT rate) it is multiplying the net cost by the VAT rate unique ID i.e. net cost is £15, using 0% should give a result of £0 VAT, but because 0% is unique ID 4 the result I get is £60!

How can I resolve this?

Please give me instructions Janet and John style as Access is quite new to be...I can be a real whizz with Excel and am very logical so have picked most things up very quickly by self teaching but this has me stumped!

Thanks in advance

Hi all,

Through years of hard work I have created a fully functioning double-entry accounts system with epos and inventory management capabilities for my retail business.

Each transaction is stored once - either in tblPurchaseInvoices, tblSalesOrders, tblPayments, tblReceipts or tblJournalEntries.

The double entry part is created automatically by queries as is all the vat/sales tax information. Producing: qryPI_VAT, qrySO_Vat, qryPaymentVat, qryReceiptVat, qryTradeDetors, qryUnpaidInvoices.

The audit trail then combines all of these queries (a total of 11!) in a union query. This is obviously a very slow process and produces 63,354 records currently. Searching for information in this list is a nightmare as are calculations!

Can anyone point me in the right direction of creating a more efficient audit trail. I would have thought one transaction table would be the way to go but I can't see how it could be done.

Thanks in advance for any help.



I'm sure this has been asked before, but I have searched and can't find the answer.

I'm writing a simple invoicing utility but want the VAT rate to be variable. I have:
tbl_invoice and tbl_vatrate. The VAT rate in the invoice table looks the rate of from the VAT table.

The problem is that trying to run a query to calculate VAT returns a rounded number and I can't stop it happening.

All the numbers in the query are set to currency, with the exception of VAT rate which is set to general. The numbers in the invoice table are set to currency, with the exception of VAT rate which is set to Double, General Number and 1 decimal place.

It's driving me cuckoo, any ideas?


I am creating an Invoicing database for a friend. In the Invoice table there is a field for VAT(%). When a new Invoice is entered I would like the form to show VAT at its current rate (15).

I have created a table VAT_Rate with a field VAT. Is it possible to point the default value to this field? Therefore the user can just change that value and not have to keep entering it in an invoice everytime.



I am trying to make a form that will take a number typed into a text box, and calculate the applicable taxes. The rate changes every year, and the information is stored in a table (beginning date, end date, int rate for that year). It would involve for say 3/21/02, calculating 365 - that date and multiplying by that years rate, adding 2003 * 2003 rate, etc.. just like regular taxes are calculated. Any ideas? I am stumped

Hi everyone,

I have a form with a control to calculate VAT, however since books are zero rated for VAT I need to calculate it for some items but not for others. Here's the code I used:

If [Books] = False Then
[VAT] = [LineTotal] * 0.175
[VAT] = 0
End If

[Books] is a yes/no check box.

This kinda works, when a book is entered into an order the VAT is £0.00 (good so far), however if you then enter an item that isn't a book into the same order the VAT is calculated for the non-book item and the book's VAT changes from £0.00 to the same value as the VAT for the non-book item, even though the two items are not the same price and so should have different VAT values.

Any assistance would be greatly appreciated.

Hi all,

I am trying to calculate variable income tax but can't seem to get it to work.

I have a query called payslips which calculates gross income based on hrs worked and payrate. I then have a separate tax table called TaxThreshWitheld which states the earnings and the tax witheld. I am trying to create a query that selects the tax witheld when earning from the taxwitheld table equals the gross field from the payslip query. Any suggestions. The two tables aren't linked and I am not sure how to link them. I tried to link "earnings" and "gross" but that causes the query not to run

Any suggestions


I have a subform listing items for purchase. Some are services and are not taxable, some are products and are taxable.

So far, I am calculating the sales tax for each record (item paid for), based on whether the thing is taxable or not and putting that result in a text box (txtSales_Tax) on the form. Since these calculated amounts aren't stored in a field, I can't SUM them, since SUM only works on data in table fields. Can someone tell me how to get a total?

Otherwise, I'll get rid of txtSales_Tax entirely, if someone can tell me how to write a SUM function, as a Control Source, that includes an IIf, or a WHERE, so it only adds items where the yes/no field Taxable=True.

I've got an IIf with a SUM function in it but, even in the footer, that only works for the current record. I need a SUM that has a nested IIf or WHERE in order to include all the records.

I’ll do the best I can to explain what I would like my program to do, maybe you can give me a little bit more guidance.

Alright – I have a database with the following 3 tables

Assets – Asset ID (PK)
Inventory – no PK
Preventative Maintenance – no PK

I created corresponding forms for each table, each with the same name.

Here is what I can do as of now:

Assets form - I am able to enter the asset #, description, serial #, hours between PM (preventative maintenance), and starting hours (same as an odometer) which I have set to 0.

Inventory form – Able to enter Part#, Asset ID (this allows the user to distinguish which asset this part was made for), Part Type, Part Description, Vendor, Location, Quantity, Individual Price, Total Price, Tax (Of the currency fields I just mentioned, I have it setup calculate the Total Price by multiplying the quantity * individual price. I also set it up to calculate 7% sales tax within the form)

Preventative Maintenance – The first field is PM# (auto number), Asset ID (distinguish which asset is being worked on), Date Checked In, Time In, Time Out, Current Hours, PM Performed, Part #, Quantity Used.

Well hopefully I haven’t just thrown a lot of information your way. So now that I have given you what my project can do, let me try and explain what I want it to do. And please keep in mind that I have been trying every which way I can to create the proper relationships, or foreign and primary keys, but that concept still passes by me at times.

I want to be able to know that if I have 10 tires for asset 1500, and I perform a PM on Asset 1500 and use 2 tires, that I will have 8 remaining. Maybe I just am missing the simple solution here, but I have been trying for days. Also, that value 8 should be the new on hand value within the inventory. I need it to update.

If I have 10 tires with part # TIR1500, and then I receive 2 more a few days later, how can I get the quantities to correspond? It seems as though I was able to work this out through queries to get the correct info, but due to the relationships I tried setting up, when I got to the PM and the quantity used, it threw my totals off.

First Hi to all!!!...
I am a complete newbie in Access 2007 so be patience with me. I am trying to do the following:

I have a checkbox in the main form that once it is checked it enebles a textbox in a subform and calculates the sales tax, if it's unchecked the textbox is unabled and sets the value to zero...(so far I have managed to do this fine, after a few trial 'n errors), but here is my problem....when I navigate to the next record using a button that goes to the next record, it does not refresh/requery or whatever it needs to do to recheck if the checkbox is checked or not and do the action/event needed.

i hope someone can help. i'm working on a sales order form. the subform lists each item purchased by the customer. i've successfully created an order subtotal which sums all the item totals. however, senior citizen customers recieve a 5% discount. the discount is listed under my customer table. how can i make a formula to calculate the subtotal * senior citizen discount?? this also relates to the tax calculation. how do i calculate subtotal * the tax rate?
sorry....i'm VERY new with access and having alot of trouble understanding the calculations. please answer asap!!

Hi All,
I use the following generally available function for rounding.
I thought it cured the problem with 'Bankers' rounding which is Access' default method of rounding.
But can someone tell me why, when using this function on the value 4.725 it returns 4.72 when it should return 4.73?
I am using it in this instance within a query to round off (to the nearest) a sales tax (vat) calc. ie:
vbaRound(Abs(qryInvoiceExport02.NetAmount)*0.175,2 )
As Im not specifying a parameter to round up or down the function will by default round off.
I've broken down the single line of code into constituent parts to see whats happening and what happens is the "Int(dblValue)" on the 3rd line contains what I expect (473) but after execution vbaRound contains 4.72!!

Am I missing something obvious here?

	Public Function vbaRound(dblValue As Double, intDecimals As Integer, _
                         Optional RoundOpt As enumRoundOpt = rNearest) As Double
'Public Function vbaRound(dblValue As Double, intDecimals As Integer,

' PURPOSE: To replace VBA's Round() function
' 1) dblValue - Value to Round
' 2) intDecimals - Decimal places to round to (any positive integer)
' 3) enumRoundOpt - To specify whether to round to nearest, up or down.
'    a) Valid argument values are
'       i)   rNearest (equivalent to 0)
'       ii)  rUp      (equivalent to 1)
'       iii) rDown    (equivalent to 2)
'       iv)  Any other number results in rounding to the nearest
' Result is a double value.
' e.g.
' vbaRound(12.3456, 2, rNearest)   = 12.35
' vbaRound(12.3456, 2, rUp)        = 12.35
' vbaRound(12.3456, 2, rDown)      = 12.34
' - http://AccessDB.Info

On Error GoTo HandleErr:

Dim dblPlacesFactor As Double
Dim dlbRoundFactor As Double
Dim dblCalc As Double

dblPlacesFactor = 10 ^ intDecimals

Select Case RoundOpt
Case rNearest    'Round to Nearest
    dlbRoundFactor = 0.5
Case rUp         'Round UP
    dlbRoundFactor = 1
Case rDown       'Round DOWN
    dlbRoundFactor = 0
Case Else        'Round to Nearest by default for all other values
    dlbRoundFactor = 0.5
End Select

'Calculate rounded value & Return value
'Original code:
'vbaRound = Int((dblValue * dblPlacesFactor) + dlbRoundFactor) / dblPlacesFactor

'Code broken down into component parts:
dblValue = dblValue * dblPlacesFactor
dblValue = dblValue + dlbRoundFactor
vbaRound = Int(dblValue) / dblPlacesFactor

Exit Function
'Leave value by default alone if an error occurs
'OR set it to any value you prefer (e.g. 0)
vbaRound = dblValue
Resume ExitHere:
End Function

I'm building a number of systems that I'm trying to make compatible in the USA and UK I have sorted all The Date And Currency Formats so the system Can Now work Completely With Both.

But At present The Systems Designed To Work With The UK VAT System Would anybody be able to give me some guidance with what I would need to make the tax system Correct for the USA As Well As The UK.

Many thanks in advance


In the UK under certain circumstances you can use the Retail Margin VAT Scheme. There are several ways to apply this scheme, the first, the VAT is calculated on your Gross Profit and you apply this rate as your effective rate of VAT. The second, and the one I use, is per transaction, the amount of VAT you pay depends on the amount of profit received for each sale that the Retail Margin Scheme is applicable. The difference between the UK and some EU countries is the sales tax on art is a fixed but substantially reduced rate.

This worked all very well until Artist Royalties (AR) came along. The wizards at DAC's decided to follow the EU model and use a banded scale based upon the Euro Day Rate which may impact on the bands to calculate Artist Royalty. It would be easy if the sales staff quoted a price plus AR, oh no, they give inclusive prices, this price is binding as it has been accepted.

To calculate the AR you need to know the Profit but to calculate the Profit you need to know the AR. Two unknowns!

The sale is £75,000 inclusive of both VAT and Artist Royalty
The cost is £50,000.

The Euro Rate is 1.3962 based on 16th November 2007
The AR bands are €50,000 @ 4% and €200,000 @ 3%
Vat 17.5% is calculated on the Profit element only
Atist Royalty is exempt from VAT

How much is the Profit?
What is the Vat Payable?
What is the Amount of Artist Royalty due?

This is the link to the AR Calculator.


I found a way in Excel to work this out but it is less than elegant and to me using Excel is like surrendering.


Hi all,

Could someone please help me with my below query? (It has been driving me nuts for a few days now! )

I currently have a formula field in a report that calculates the individual VAT for a list of values. The formula is as follows- ({Orders43.OR_NETT}*0.15) I have also tried ({Orders43.OR_NETT}/100*15). The problem I have is that when I export this report and total up the column which contains all the individual values in excel it comes to 8883.6045 HOWEVER if I export this report without the formula field and do this manually in excel in its place the value is 8883.3945. To recap- Total when crystal works it out is 8883.6045 and total when worked out on Excel is 8883.3945.

I really hope someone can help me please?

Thank you in advance

PS. Hello all I'm new here!


i have a customer form which includes a "Country" combo box.. the reason i did this is because i have a separate table with a list of countries and their VAT (Value Added Tax, you americans call it sales tax i think ) rates.

i want this country saved in the customers table.. i need the country table to act merely as a source of data. Now ive spent a good hour tying to fix it messing around with the relationship between the two tables and the combo box itself.. but ive only been able to come to two end results

a) access tells me i cant save the new customer record cause a related record is required in the country table.

b) access enters a new line in my country table with the autonumber of the country selected on the add new customer form in the Country field.

im stumped as to where the problem is:// how can i fix it?

Dear all,

I have a query that is based on orders table and contains "Qty", 'UnitCost', and "ctualUnitCost", in addition to yes/no control to indicate if "SalesTax" is applicable.

I want an expression in the query to calculate total cost in one of 2 conditions:
if no sales tax then TotalCost = Qty*UnitCost
if sales tax is applicable then Total cost = Qty*UnitCost*1.1

how can I write this expression


Please help me with this query!

I've got a table "orderitems" that allows people to enter unit prices, # of units, and then select a tax code from a combo box. The combo box gets values from a tax table which has three fields: tax_code, tax_desc, and tax_rate. Taxes available are NONE, GST, PST, GST&PST.

When I calculate an order's total (for the purpose of creating an invoice), I need to know the value of the PST and the value of the GST separately. I can't figure out how to do it. Any suggestions? Maybe my design is poor.

I can easily write a query that calculates the total tax, but I can't figure out how to get Access to give me one column for PST and one column for GST.


I am a novice user of Access 2007. I have a form 'frmStock' based on a table 'tblStock'. The table is linked to another table of sales invoices, 'tblSalesInvoiced', which has the dates of sales. The primary key of 'tblSalesInvoiced', 'InvoiceNumberID', forms a field in 'tblStock' and appears in 'frmStock' with a combobox displaying the respective sales invoice number. I want to insert a calculated field into the frmStock which makes use of the date of sale from the table of sales invoices - this will allow me to use a change in tax rate from a particular date in calculating sales tax due on items sold. I am unable to set up a text box in the form bound in such a way as to display the sale date. Any advice as to how to set this up?

I need to know how to take the value returned by an expression in a calculated field and place it in another 'static' field that doesn't update itself when the underlying data in the original expression changes.

This is the application: Ten years ago wrote an invoicing program that calculates and adds sales tax (from various combinations of three jurisdictions) to each invoice. The problem is that sales tax rates (which are stored in a 'sales tax table') recently changed - something I didn't anticipate when I wrote the database.

If I update the tax rates in the sales tax table, older invoices are also recalculated using the new rates. Obviously, I need the invoices written under the prior tax rates to remain unchanged.

I am a beginning to intermediate-level user. Am I on the right path or missing an entire concept for handling this type of data?

Thanks in advance for any help!


I have a textbox on a form that calculates VAT for transactions whose controlsource property is :


The trouble I am having is with the rounding that seems takes place in the control. If the fractional part of a value is calcuated it is rounded in accordance with the following :

When the fractional part is exactly 0.5, control always rounds it to the nearest even number. (For example, 0.5 rounds to 0, and 1.5 rounds to 2).

i need to ensure that the VAT is rounded (either up/down) in ALL cases. Any ideas people?



Not finding an answer? Try a Google search.