Calculating VAT

[SteveF]

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?

 

[PNGBill]

Is your problem just the rounding - ie, nothing to with VAT as such and or the calculation of the variable VAT rate??

If just rounding, how is VAT calculated, by line item or Invoice Total?

 

[SteveF]

It's calculated by the total. So I have an amount, less discount = subtotal, subtotal x vat rate theoretically equals vat amount

 

[SteveF]

I should say that I've had no problems with this before but have hard coded the vat rate in the past. That proved to be a mistake, and a royal pain in the butt to get over, so this time I'm trying to do the thing properly.

I'm suspicious of the 'vat rate' number.

 

[vbaInet]

You may find this thread useful:

http://www.access-programmers.co.uk/forums/showthread.php?t=161410&highlight=VAT+calculation

 

[SteveF]

You may find this thread useful:

http://www.access-programmers.co.uk/forums/showthread.php?t=161410&highlight=VAT+calculation

That doesn't help, though thanks for looking. I can do the maths regarding vat, this is a different issue I think.

Access is rounding the number calculated up and I can't see why.

If I calculate vat off the subtotal directly rather than the vat rate number pulled from another table it works fine. This has to be something to do with the vat rate coming from a vat rate table rather than being hard coded.

 

[vbaInet]

Can we see what the output looks like?

Use: CCur(Round(calculation here, 2))

 

[SteveF]

Here goes, see if this uploads ok.

I have the calculation: VAT Amount: [Subtot]*[VAT_Rate]/100

 

[SteveF]

Can we see what the output looks like?

Use: CCur(Round(calculation here, 2))

That rounds it to £2.60.

 

[vbaInet]

So the VAT_Amount should be the same as Expr1 right? You're VAT_Rate is returning 2 from my calculations. Are you using a subquery?

Are you able to post the problem part of your db?

 

[SteveF]

That's right, it should be the same.

There is no sub-query, the VAT rates are input straight into the table by a vat rate form. From there, there is a lookup field in the invoice table to pull in the appropriate vat rate.

I think I'm doing something dumb with the vat rate number, but I can't see what.

How would I post part of the database?

 

[vbaInet]

You mentioned using a lookup field. Have a look at this link re lookup fields:

http://www.mvps.org/access/lookupfields.htm

Looks like something wrong with the value that VAT_Rate is returning.

To upload:

1. Remove sensitive and keep some erroneous data for testing
2. Perform a Compact & Repair
3. Click the GO ADVANCED button and you will find a button call MANAGE ATTACHMENTS
4. Browse for you db, Upload, wait for it to appear on the small window
5. Close the window and REPLY

 

[SteveF]

I've exported the tables and query to a new DB. The problem is still there and more convinced now that it is the vat rate that is the problem.

 

[SteveF]

You mentioned using a lookup field. Have a look at this link re lookup fields:

http://www.mvps.org/access/lookupfields.htm

Looks like something wrong with the value that VAT_Rate is returning.

To upload:

1. Remove sensitive and keep some erroneous data for testing
2. Perform a Compact & Repair
3. Click the GO ADVANCED button and you will find a button call MANAGE ATTACHMENTS
4. Browse for you db, Upload, wait for it to appear on the small window
5. Close the window and REPLY

Thanks for your time on this by the way; it's appreciated.

 

[SteveF]

So reading that link lookup fields = bad. What is the alternative?

 

[vbaInet]

So reading that link lookup fields = bad. What is the alternative?

Good point Steve. Just reminded me to look in the lookup field. Remember I mentioned I calculated it was returning 2? That 2 is the Vat Rate ID associated with the Vat Rate 17.5. See why lookup fields aren't good?

Perform the link directly using the Relationships window.

 

[SteveF]

Ha! I knew it was that vat rate! So it's the vat rate rounding first and then the query calculating the rounded figure? I will try and fix it.

 

[vbaInet]

It's not rounding. This is the thing about lookup fields, the VAT_Rate that is being returned for calculation is the Vat Rate ID, not the Vat Rate itself. So instead of it picking the second column of that field, which is your VAt rate, it's picking the bound column which is Rate ID. Do you get it now?

 

[SteveF]

I think the penny is dropping. I am now trying to create the correct relationship between the two tables...

 

[vbaInet]

Great. So in that query you will now need to join the vatrates table with your invoices table and include the VAT Rate field from the vatrates table.