Forum Discussion

Brass Contributor

Apr 14, 2020

VAT calculation

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

Access 2016

luise983
Jul 19, 2024
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.

Gustav_Brock

Brass Contributor

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)

indiajetfer

Copper Contributor

Mar 13, 2023

Thanks for the information.

Forum Discussion

VAT calculation

Share

Resources