Rounding decimal places properly

[DeanRowe]

Hi,

I am trying to multiply £127.65 by 1.175 to produce and store the answer £149.99 in my “answer” field.

I have spent more than a few hours testing different methods, but none so far seem to be suitable.

I used to use the currency data type, however - although the field would show £149.99 – it is stored as £149.98875. So when it comes to adding up each of the “answers”, I am sometimes a penny out because it is adding up the stored number and not the two decimal place answer.

I then changed the data type to “number”, set format to currency, and set the scale and decimal place settings to 2. However this is now not rounding the numbers up correctly and the answer produced is £149.98.

Is there a way I can round up the numbers, and store them as £149.99?

Any help would be greatly appreciated.

Thank you for your time.

Dean

 

[RuralGuy]

I would recommend that you leave the fields as Currency. To round the field to two decimals you must do that with Round([YourField],2) so that the field is actually changed and not just the way it is displayed. IIRC, Round() uses bankers rounding.

 

[DeanRowe]

Thank you for your help rural guy. This may sound like a silly question, but where should I enter the code to do this? Can it be set to the table field properties somehow? Do I add it to the properties of the text box on the form? Or is it to be used on a query somehow?

Thank you again, I'm looking forward to getting it working!

Cheers, Dean

 

[RuralGuy]

Probably the best place to round the value is where the value is being stored in the first place. How are the values entered into the table?

 

[DeanRowe]

I run a query on a subform to calculate nearly all the calculations, especially to calculate quantity prices and the tax amounts on quantity orders. I use the code quite a few times using different methods, but if it can be used on a query I'll be quite happy to change the other methods so it's all peformed like this.

Thank you for your time Rural Guy.

Dean

 

[RuralGuy]

As you know, where you round makes a difference. The Round() function can be used in a query and storing calculated values is rarely a good idea. If you need help in incorporating the Round() function in a query then post the SQL for your query and indicate which field you need rounded.

 

[DeanRowe]

I've just read a thread that explains my problem a little better...

...you can calculate the VAT by multiplying the Price/quantity by the VAT rate [i.e. Price * 1.175].... but the VAT is likely to involve rounding off pence fractions, and you may therefore decide to store the rounded VAT

Note that if you have several lines on an invoice[WHICH I DO], the VAT calculated line by line may be different to the VAT that would be calculated if you worked it out on the whole invoice[THIS IS A MAJOR PROBLEM FOR ME]. This is then made more awkward if not everything on the invoice is rated at the same VAT rate[ANOTHER PROBLEM].

...you wont "see" any obvious errors as its all done behind the scenes. Therefore your system needs to allow for any eventuality."

I'VE HAD LOADS OF PROBLEMS WHEN MY QUERY TOTALS UP THE VARIOUS LINES ON MY ORDER USING THE UNROUNDED, STORED NUMBERS - SO THE FINAL TOTAL TURNS OUT TO BE DIFFERENT THAN THE TOTAL IT SHOULD BE IF YOU ADDED UP THE ROUNDED NUMBERS SHOWN ON THE FORM.

Sorry for the CAPS, I caught the button and didnt realise.

Here are a couple of lines from my query that I use for calculations, that are entered into the top line/field section of the query in design view:

Selling Inc VAT: [Selling NET]*[VAT Value]
Total Buying NET: [Buying Price Net]*[Quantity]

Thanks again Rural Guy.

 

[RuralGuy]

Try:
Selling Inc VAT:=Round([Selling NET]*[VAT Value],2)

 

[DeanRowe]

I'm trying not to get too excited, but so far so good, I'll give it some thorough testing tomorrow and come back and let you know.

Thank you for your help Rural Guy, I really do appreciate the time you've spent helping me tonight, I've built my database myself and have been living with this problem since February and have spent countless hours trying to fix this... fingers crossed it works.

Thank you again mate.

Dean

 

[RuralGuy]

You are very welcome Dean. Post back here if we need to do a little more tweaking on your code.

 

[Simon_MT]

I know from experience that whilst the Vat Man may notice that Vat maybe a pence out, I've never been asked to re-imburse the Vat Man with a few pennies. I deal with both Margin Vat, Zero Rated, Zero Rated for Export and Full Vat transactions with Foreign Currencies added for good measure. Margin Vat is essentially VAT based upon the amount of profit and it is not disclosed. So each and every Invoice line item, Vat is calculated individually. This still creates problems with rounding this is what I do:

.[VAT Amt] = CLng((.[Profit Amt] * .[Currency Rate]) * .[VAT Rate] * 100) / 100

To overcome the rounding issue users are permitted to adjust the Vat Amount on the Entry screen. It is important to get in our case the principle mechanics of invoicing correct.

Simon

 

[RuralGuy]

Issues with the Vat Man to one side, your formula will truncate at two decimals rather than round off the answer. Still, probably ok with the Vat Man.

 

[Rabbie]

I have seen VAT notes that say you will get rounding differences depending on whether you calculate the VAT item by item or on the total invoice. They aren't bothered about small rounding differences.

 

[neileg]

Note that if you have several lines on an invoice[WHICH I DO], the VAT calculated line by line may be different to the VAT that would be calculated if you worked it out on the whole invoice[THIS IS A MAJOR PROBLEM FOR ME]. This is then made more awkward if not everything on the invoice is rated at the same VAT rate[ANOTHER PROBLEM].

This is a well known issue. The VAT regulations allow you to calculate VAT line by line or on the invoice totals, grouped by VAT rate. This will produce rounding differences, but HM Revenue & Customs are happy either way.

It's more of an issue when you are capturing other people's invoices, since you can't determine which way they have calculated the VAT. Just to complicate matters, if the supplier offers a settlement discount, the VAT may be calculated on the discounted price. Again, perfectly acceptable to HMRC.

However, they are bothered if you try and recalim an amount of VAT that differs from that show on the tax invoice, whatever the cause of the difference.

 

[Simon_MT]

I believe that unless you have apply full VAT to every transaction, VAT could be calculated on line items as some products are Exempt from VAT. We can not produce Invoices, save for Exempt Items, with both Margin VAT and Full VAT.

I agree with RuralGuy, Rabbie and Nieleg, there is a small tolerance with VAT due to rounding differences. As my Invocing system is complicated, I allow the VAT amount to be adjusted, only so that on the odd occasion there is a penny difference, it can be corrected by the user.

Simon