VAT calculation

[Storming]

Hi, I want to calculate VAT (generally 20%) from a gross amount.

I want to be able to format it to 2 dec places so that if the pence is .100 to .104 the pence stays as .10 but if the pence is .105 to .109 the pence increases to .11.

I also want to be able to export the VAT as a currency so that it can be summed etc in Excel and not exported as text.

I have come up with the following Access query function which seems to work.

CCur(FormatCurrency(([Gross Amount]/(1+[VAT Percentage]/100)-[Gross Amount])*-1))

To explain my suggestion:-

The VAT calc is a standard method calculating VAT from gross.

I then format it as currency to truncate to 2 dec places but this changes it to a text value so I then carry out the ccur function which then converts it to currency for downloading to Excel.

This appears to work but seems a long way round.

Can anyone verify that the calculation is correct or there is any better way to do it.

I am unable to use the round function as this uses the bankers rounding which calculates the wrong dec places at times.

[#2]

@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.

[#3]

@George Hepworth 

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.

[#4]

@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

[#5]

@George Hepworth 

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.

[#6]

@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.

[#7]

Hi George, just wondering if you could help me with another problem.

I am trying to open a Word document containing the user manual at different bookmarks depending on which form the user is on.

I have the following code:-

Private Sub Command35_Click()
Dim strPath As String
strPath = "C:\Users\norma\Desktop\Phili CCC business management system user manual.docx"
Dim objWord As Object
Dim docWord As Object
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
Set docWord = objWord.Documents.Open(FileName:=strPath, ReadOnly:=True)
docWord.Bookmarks("MainMenu").Select
objWord.Activate
WindowState = wdWindowStateMaximised
End Sub

The Word document opens fine but I can not get it to be maximised.

The other problem is that the Word document opens at the bookmark but the bookmark is at the bottom of the maximised screen showing half of the previous page above it. See attachment.

Any idea how to resolve that please so that the bookmark starts at the top of the page?

[#8]

@Storming Unfortunately, automating Word is not in my skill set. I suggest you re-post this question in the Word forum where Word experts can see it. Sorry.

[#9]

Check out VAT here online https://www.vatcalculator.info/mehrwertsteuer-rechner/

[#10]

@Storming I would use Format:

CCur(Format(GrossAmount * (1 - 1 / (1 + VatPercentage / 100)), "0.00"))

or the RoundMid function from VBA.Round :

RoundMid(GrossAmount * (1 - 1 / (1 + VatPercentage / 100)), 2)

[#11]

Thanks for the information.

[#12 · Accepted solution]

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.

[#13]

Hi,

A warning to you and the OP who marked your suggestion as best response:

You should never include the Round() function into anything that has to do with money, because it does not round commercially but uses mathematical or so called banker's rounding, i.e. 5 is rounded down to the nearest even number. This is an ancient problem of the Round() function and discussed a million times since it was introduced with Access 2000.

Therefore Gustav in his suggestions uses the Format() function or a user defined function to round commercially and avoids the rounding errors that happen with Round().

Servus
Karl
****************
Access News, Forever, DevCon
Access-Entwickler-Konferenz AEK - 19./20.10. Nürnberg

[#14]

@luise983 

not sure why people are now replying to my original post which was 4 years ago.

[#15]

Hi,

I don't know why you got the reply by luise 4 years after the discussion. I replied to warn everybody who reads it (and especially as it is marked as "best response"), that it uses the buggy Round() function, which nobody should use, let alone when calculating with money.

Servus

Karl

[#16]

@Karl_Donaubauer 

Is there any way I can close the post?

[#17]

NO MORE RESPONSES PLEASE, THIS IS AN OLD THREAD.