Find VAT in Access ?

Question

Hi folks im tryin to find VAT in query (Access) the formula i used is like this:

VAT : [cost]*17.5/100
(cost can be any cost eg. 120)(17.5 VAT rate)

problem is every time i run query a box appears and ask about VAT value i already gave the value which is 17.5. Can any one help me with it.
cherrrrrsssssssss.

sorry i didnt mension that cost is another field

field data

cost 120
vat [cost]*17.5/100

Answer 1

It's difficult to answer this question without seeing your database and query.

But, supposing you want to pull a query in which you computer the VAT yourself from the cost field, you can enter this in SQL view:

SELECT cost, (cost * 0.175) AS vat, (cost * 1.175) as vat_total
FROM table

That will give you two columns: the cost field, and a field named vat that has calculated your VAT, and finally, a column named vat_total, which is the cost plus the VAT amount.

If you want to round the result to a precision of 2 (currency), make it this:

SELECT cost, ROUND((cost * 0.175), 2) AS vat, ROUND((cost * 1.175), 2) AS vat_total
FROM table

The one problem with this is that Access will not round up the final decimal if it is less than 5. So, if (cost * 0.175) = 2.342, ROUND will return 2.34, not 2.35, which your tax authority may want.

You can compensate for this by always charging an extra penny:

SELECT cost, (ROUND((cost * 0.175), 2) + 0.01) AS vat, (ROUND((cost * 1.175), 2) + 0.01) AS vat_total
FROM table

Or, unlike me, you could be not so lazy and create a function that returns the proper amount.