Forum Discussion
VAT calculation
Your method works, but you can simplify it by using:
```sql
CCur(Round([Gross Amount] * [VAT Percentage] / (100 + [VAT Percentage]), 2))
```
This VAT Calculator method keeps it as currency and exports correctly to Excel.
Storming It appears that your solution is probably the most EFFECTIVE way to do it, although it may not seem to be the most ELEGANT way. Sometimes Access is like that.
Thanks George,
did you have time to test it at all or did you just use your vast knowledge to check the format/syntax etc
Do you know any better ways of doing it?
I could not believe the time I spent looking for other solutions on the net but could not find any.
I assume other people just use the ROUND method which in some scenarios does not calculate it properly.
Storming I read the calculation and can think of no BETTER way to do it. There are, no doubt, other ways. At this point in my career, I give precedence to methods that WORK over methods that involve lots of elegant, complicated code. If that gives you the result you need, it's good. Otherwise, you could invest additional hours in a solution that may or may not be better.
Access does do rounding in a way that doesn't always work for every circumstance.
I once had to work on a similar problem for a client who was doing sophisticated performance testing for sports equipment. They needed rounding as you do. I came up with an approach similar to yours to calculate it as they wanted/needed it for their reports because one test out of many dozens would be off as far as they were concerned due to Access default rounding. As I recall it was a bit more involved than your (relatively) simple solution. Whatever works.If trial and error tells you this method works as required, and does so reliably, why invest additional time looking for something else.
I
Hi George, thanks for your help again, it is certainly appreciated.
I could just not believe that someone had not posted a solution before to this problem as surely a lot of businesses would be required to use the same calculation.
Storming I strongly suspect that most of the time, people who have confronted this problem have just worked out a solution as you were able to do.
