Dealing With VAT

[D Willett]

I'm after some idea's from members on dealing with VAT(Tax) in databases.
It's quite easy to just hardcode or set a default value and calculate the sales price by whatever figure is set.
But, thinking forward, if VAT changes then historical data could be affected before a certain date, so I have to think hard with this one and get it right now rather than have a massive head ache later.
Most of our sales items, ie Labour, Materials etc induce a VAT Tax levy of 20% but some items are VAT free, such as Vehicle MOT, Fuel, Police Recovery.

So a lot to think about and getting it right is important.
I have currently set a table aside "tblVAT" with two codes: 0 and 1, 0 is VAT free and 1 is Whatever the VAT rate is set at ( 20 in this case ).
So if the VAT rate changes I can easily change the one value in the table.

But what about historical data? If it is re-calculated then I have a problem?

So I'm just wondering how other users or designers have combated this subject..
Cheers

[HansV]

In such a situation it is OK to store the VAT amount in the table instead of always calculating it on the fly.
So, create a table tblVat with a single field VATRate of type Long Integer, and create a single record in it with the current VAT rate (20).
In your table with sales items, create a new Yes/No field named WithVat (or similar). Set it to Yes for items such as Labour and Materials, and to No for Fuel etc.
On the form bound to the sales table, use the After Update event of the controls associated to sales items to retrieve the current VAT rate:

DLookup("VATRate", "tblVAT")

and if WithVat is True, calculate the appropriate VAT and store it in the VAT amount field.
Since VAT amount is not a calculated field, it will not change if the VAT rate changes later on.

[D Willett]

Great stuff Hans. Thanks for the advice.
Kind Regards

[D Willett]

Hi Hans
I'm having to pull the VATYesNo from the items table and on the subform refer to the CmbOtherCode.column(2) to get the status of Vat.
I'm confusing myself here trying to calculate the amount and Save the amount to the table.

Any chance of having a look?

Cheers

[HansV]

You could add the following code to the After Update event of the Charge and cmbOtherCode controls:

Code: Select all

  If Me!Text30 Then
    Me!VATamount = Val(Me!Charge) * DLookup("VATRate", "tblVAT") / 199
  Else
    Me!VATamount = 0
  End If

Note that this will NOT fill in the VAT amount retrospectively - we want to avoid that! But when you change the charge or code, the VAT amount will be calculated.

See the attached version:

VAT-Test.zip

[D Willett]

I think I've got it now.
I was getting type mismatch with the after_Update of the combo so just applied the code to the After_Update of the [Charge], it seems to be happy now.
The code I'm using ( changed "Text30" to VatYN ) is:

Code: Select all

Private Sub Charge_AfterUpdate()
  If Me!VatYN Then
    Me!VATamount = Val(Me!Charge) * DLookup("VATRate", "tblVAT") / 100
  Else
    Me!VATamount = 0
  End If
End Sub

Does this look ok and can you see any foreseeable problems?
Thanks Again

[HansV]

That looks OK to me!