Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2008
    Posts
    49

    Unanswered: validation for vat

    hi, im using validation in my database. ive got one quick query.

    basically i have a table for payment containg the attributes total, vat and total inc vat.

    I will enter the total each time for every record myself. I want the vat feild to always be 17.5% so i have used a default value of 17.5.

    i want the total inc vat to automatically add 17.5% onto the total.

    What is the validation rule which i must use.

    thanks

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    1. You shouldn't do what you are trying to do.
    2. The default value should be 0.175 not 17.5.
    3. Validation Rule should (and I hesitate to use that word) be [Attributes] * (1 + [VAT]).

    I repeat though, what you are doing is completely unnecessary. You need only store the [Attrib] field in the table. Everything else can be done with formula based calculations in a query or form. If the VAT <might> change, then you should probably store that as well, however, manually entering the INCVAT total is just a complete waste of time.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Addition

    Watch your rounding carefully.

    We had that same prob. in the past

    espesily when your gonna enter values that are allready inclusiv the vat,
    and your gonna reverse the calculations
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    erm why are you using a default value of 17.5% for VAT? it doens't make any rational sense.....

    assuming you are UK based
    ..there are four VAT categories applicable in the UK at present, exempt, zero rated, fuel and standard. VAT applies to individual products and services. so you cannot just assume that adding 17.5% to an invoice gives your the correct VAT amount.
    where you do your rounding is often as important as how you do your rounding, providing you are consistent you should be able to prove to HMRC that the calculation is correct. In my books VAT should be calculated at row level, ie int((quantity * price * VATRate)*100)/100 then the total of VAT is the sum of the rows.... the reason, aside form its the right way to do it, it also favours the organisation rather than those thieving !!!! at HMRC. WHy am I so cynical about HMRC, had a VAT inspection once, they demanded that we charge VAT on the postage element of postage and packing, so just to pish 'em off I split postage and packing into two elements, postage (NO VAT), packing (standard rate).

    So in my books you need a table of VAT codes
    You probably may want to consider the implications of how to handle changes in VAT rates or introduction of new VAT Codes, or products moving from one VAT category to another.

    the VAT is actually a derived or calculated field, the VAT code applicable to a specific product should come from the product table, the rate from the rate table, and I'd strongly suggest you break the rules of normalisation and store both the VAT code, rate and the VAT amount in your invoice. otherwise you have a huge flaw in your system which HMRC will try to exploit to stiff you for more tax. It helps if you make your invoice table write/insert only.

  5. #5
    Join Date
    Feb 2008
    Posts
    49
    hi,

    the vat amount will always remain 17.5%. i want the vat amount to always be 17.5% or 0.175 as methinoed above. the Total inc Vat feild should be automatically created.

    when normalising i removed the total inv vat feild as its a derived feild but i want it in the table as it is needed when making reports.

    thanks

  6. #6
    Join Date
    Feb 2008
    Posts
    49

    vat

    hi, i have uploaded a screen shot that may make what im saying a bit more clear.

    i need the formula/validation rule so that vat will be automatically caluclated from the totoal amount. the totoal inc vat feild should also be automaticalyy caluclated by adding the total and vat feild.

    thanks
    Attached Files Attached Files

  7. #7
    Join Date
    Feb 2008
    Posts
    49
    also i need the formula for d.o.b must be at least 21 from todays date.
    I used <1987 but in a years time that will change as someone 20 will be 21 then.

    thanks

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by aa_86
    ...the vat amount will always remain 17.5%. i want the vat amount to always be 17.5% or 0.175 as methinoed above.
    bad bad idea
    why?
    ...you do not control VAT rates
    ...you do not know when VAT rates may change
    ...as mentioned before there are at present 4 categories, you don't know if or when a new category may be introduced.

    you cannot afford to hardcode the VAT rate, esepcailly when the solution is relatively painless.

    Quote Originally Posted by aa_86
    when normalising i removed the total inv vat feild as its a derived feild but i want it in the table as it is needed when making reports.
    thanks
    for an invoice you need to break the normalisation rules, becuase the VAT (and price) applicable to the product are those applicable at the time the bargain is made, your accounting must take note of that. trying to construct a data model that tracks changes in price or VAT is madness.

    the answer to your problems is to put come code behind the form that captures your sale item
    presumably you validate that the user has specified a product and specified a quantity, you are pulling your VAT rate from a VAT table, based ont he VAt rate in the product table, you are pullign the price from a product table.

    so you must have a product, a quantity ordered
    as part of the before update event calculate the line value (qty*price) and VAT Amount (VATRate * Line Value).
    you don't need to store the line value, nor do you need to store th VAT (however you would need to store the VAT amount or the VAT rate)

    look in the forms before update event.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    to validate a year value use the datediff, or dateadd function

    probably best to put it in the relevant controls before update event

    consider using a date control to coerce the user to enter a valid date.. it will save you a lot of work in making certain the vlaue supplied by the user is actually a valid date

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by aa_86
    Total inc Vat feild should be automatically created.
    Then you don't need a Validation Rule.

    Quote Originally Posted by aa_86
    when normalising i removed the total inv vat feild as its a derived feild but i want it in the table as it is needed when making reports.
    And the report can't derive the value again?

    Seriously, you are expecting way too much from the table design window. BTW, the screenshot tells us nothing beyond what you have already stated.
    Last edited by StarTrekker; 03-06-08 at 20:07.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Feb 2008
    Posts
    49

    Date diff

    I have a field in my customer table called DOB. Here the user enters the customer's DOB. The business rule is that the customer must be 21 or over.

    I want to use validation so that the diffrence between DOB and todays date is caluculated and if the customers is 21 or over than it is accepted otherwise error message displayed " minimum age must be 21"



    I am trying to use this formula but i just get an error message

    DateDiff("yyyy",DOB,Date())

    any help please, ive seen this used for queries but no validation so far

    thanks

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Look at the DateAdd function and the < operator
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Quote Originally Posted by aa_86
    hi, i have uploaded a screen shot that may make what im saying a bit more clear.

    i need the formula/validation rule so that vat will be automatically caluclated from the totoal amount. the totoal inc vat feild should also be automaticalyy caluclated by adding the total and vat feild.

    thanks
    I'm getting off track but

    just been look at you screen shot

    Just one thing i say

    Take the spaces out of the feild name

    and use the Caption above default value

    ms is brainee that it will display the caption when needed

    also when you start writing SQL


    you have to write some like

    [invoice].[Total inc VAT] your current way

    if no spaces just need to write

    invoice.vat

    just some pointers

    less typing and spelling mastack
    Last edited by myle; 03-06-08 at 23:48.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  14. #14
    Join Date
    Feb 2008
    Posts
    49
    does anyone have the validation to only accept dob value, if customer is 21or over. thanks

  15. #15
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're gonna need to study expressions!!

    Validation Rule: <= DateAdd("yyyy",-21, Now())
    Validation Text: Must be over 21 years old to proceed.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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