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

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.

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

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.

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

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

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

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.

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.

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

Then you don't need a Validation Rule.

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.

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

Look at the DateAdd function and the < operator

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

does anyone have the validation to only accept dob value, if customer is 21or over. thanks

You're gonna need to study expressions!!

Validation Rule: <= DateAdd("yyyy",-21, Now())
Validation Text: Must be over 21 years old to proceed.