Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    VAT Change (2007 SP1)

    As some people may know, VAT has changed in the UK from 17.5% to 15%. I'm in the process of creating a small Purchase Order Database, and I've often read that you should not store totals, etc, in Fields but instead use Calculations in Queries, Forms, etc.

    Hans very kindly provided the Formula for doing this on a Form, but with the change of VAT, will this not throw records out in future when VAT changes back to its normal rate.
    Eg
    Someone buys a Product at 100. This is stored in a Value Field. If VAT is to be included, then the Calculated Total Value displays as 115.00.

    But, eg, in 2 years, if VAT reverts to 17.5%, then this Calculated Total will display as 117.50, which wouldn't be right, as only 115.00 was paid for the item.

    What's the best way to circumvent this problem? Should I just go ahead and create a Field called Total?

    If so, do I then use an OnClick Event to Update this Field, eg.

    Dim db As Database
    Dim strSQL As String
    If me.VAT = true Then
    Set db = DBEngine(0)(0)
    strSQL = "INSERT INTO [tblPurchaseOrder] ([TotalAmount]) VALUES('[InvoiceAmount]*(1+IIf([VAT],0.15,0))');"
    db.Execute strSQL
    Response = acDataErrAdded
    Set db = Nothing
    Else
    Response = acDataErrContinue
    End If

    I could then just change this Event Code whenever VAT changes?

    BTW, I don't think the above code is exactly right, so suggestions are appreciated!

    Thank, as always, in advance for any help or advice.

    Brian

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: VAT Change (2007 SP1)

    I store the VAT (or GST) rate used for each transaction in a field associated with the transaction record. So calculations are based on the transaction VAT(GST) field, rather than the more general current value.
    Regards
    John



  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: VAT Change (2007 SP1)

    Thanks John

    I've changed the Yes/No Checkbox to a Number Field and have set the Default Value to 15, so the Total Amount is now a calculation of those combined.

    Thanks for your suggestion.

    Take care

    Brian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •