Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2008
    Posts
    4

    Unanswered: Help needed to change VAT rate in database

    Any help would be appreciated...

    I have a database which was written by an old friend. As the VAT rate has changed i need to reflect this in my database. I have changed as much as i can see in the database from 17.5 - 15%, onscreen everything is ok but still when i print the invoices the prices come with the old 17.5 rate.

    Any help would be much appreciated.

  2. #2
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    That's a hard one to troubleshoot here with out knowing much about the database design/structure, etc. It sounds like the % might be hard coded into the report itself or maybe in the query the report is based on.

    Try looking at those things and let us know if you've had any luck.

    C

  3. #3
    Join Date
    Nov 2008
    Posts
    4
    to tell youthe truth i wouldnt know where to look tofind the hard coded stuff as i am not too good with access databases.

    The guy that designed it has done a runner

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    vat code hardcoded.. surely not


    check the db tables
    have a look at the tables see if their is one for VAT rates

    look at the report / form that is displaying the 17.5% rate and work back from there. work out where the 17.5% value is stored.

    ideally I'd expect a product to have a VAT code
    and that VAT code is the FK to a table defining VAT rates.

    see if there is a hardcoded value in a module somewhere
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2008
    Posts
    4
    i have looked everywhere guys but i have come to a dead end.

  6. #6
    Join Date
    Nov 2008
    Posts
    4
    Can you guys tell me what this does:
    Else
    DoCmd.Requery "CurrentTotal"
    Me!VAT = IIf([txtDesc] = "Used Tyres", 0, Round([CurrentTotal] * 0.15, 2))
    Me!NewPrice = Me!CurrentTotal
    SendKeys "{F9}"
    DoCmd.GoToRecord , , acNewRec
    DoCmd.GoToControl "cboTypeList"

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    that's a hard coded VAT calcualtion
    yuck!
    who ever wrote this was truly no friend of yours or anybody els efor that matter

    and worse its specific to a product that its doing a text match on

    effectively there is a test to say if the product is "Used Tyres" then the VAT rate is 0 otherwise the VAT rate is 15%.. multiplying the exvat price by 0.15 is the same as by 15%.
    ie if the product is not "Used Tyres" apply a VAT rate of 15%

    round the calculation to 2 decimal places and assign the value to a control called VAT

    so if you had a product called say "special used tyres".. it would calculate the VAT, or "tyres, used", I;m pretty certain it will attempt to apply VAT on any products called "used tyres".... providing used tyres is the only VAT exempt product you sell then you may be OK.. providing you make sure you sell the product described as "Used Tyres", and not "used tyres", "USED TYRES"...

    there are two issues here
    first you have to adjust your prices for Monday morning or those charming gentlemen & ladies from HMRC will be on your case.

    if you are certain the vat calcs are correct (and Im not certain they are) then I'd search every form & report for a reference to 17.5, or .175 and change it to 15% or .15.

    the other issue is you have to redesign this crock of sh1t. there are 4 VAT rates applicable to UK sales transaction at present
    Zero rated
    Exempt
    Fuel Rate
    Standard Rate
    there is nothing to stop the comedians in power introducing additional rates as required. given the current state of UK financs I wouldn't be at all surprised if there is a new rate invented next year for say 'luxury' goods.

    The model I'd expect is a VAT table containing a Code, a description and a rate
    a product table which has the VAT code in it, and a RI/relationship/FK to the VAT table. so when you select a product it automatically pulls throuh the correct VAT rate (assuming you do this with a query).
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Hmmmm...hard coded.

    C

  9. #9
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    I would like to add a little postscript to healdem's specification for a VAT lookup table. Table values change, so if you expect to revisit older records then you must do one of two things. You can either capture the rate valid at the time and store it in every record which uses the rate, or you must add a date range to the lookup table to indicate the validity of the rate. Current values have a null ActiveToDate. When the value changes, the ActiveToDate is filled in to terminate the currency of the record and a new current record inserted with a null ActiveToDate. Usually, this can be generated automatically with VBA, so the effort is not great. Then, any report or query which needs to lookup the VAT for a transaction can ask for the value for which the transaction date falls within the validity range. This is not an absolute requirement, but it is extremely useful when preparing annual reports or doing projection analyses, for example.

Posting Permissions

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