Forum Discussion

Storming's avatar
Storming
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's avatar
    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's avatar
Gustav_Brock
Iron Contributor
Aug 03, 2021

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's avatar
indiajetfer
Copper Contributor
Mar 13, 2023
Thanks for the information.

Resources