Changing Round to Int

[KenshiroUK]

Hi all I currently have =Round(Sum([Extended Price]*1.2-[Extended Price]),2)

however I want to Int instead because of the third decimal place.

 

[plog]

You're currently using the Round function. So check that out to see how it works:

http://www.techonthenet.com/access/functions/numeric/round.php

 

[pr2-eugin]

Not sure if you want Int here.. Int function truncates the decimal part and returns only the Integer part..

? Int(19.8653)
19
? Round(19.8653,2)
19.87

? Int(19.99)
19
? Round(19.99)
20

What is your exact need?

 

[KenshiroUK]

I'm not sure where its going wrong then. I mean I have

Subtotal - 115.34
VAT - 23.07
Charge: 0.00
Total: 138.40

it seems to be a penny out.

I have VAT - =Round(Sum([Extended Price]*1.2-[Extended Price]),2)

Total - =Round(Sum([Extended Price]*1.2-[Extended Price]+[Extended Price]+[FreightCharge]),2)

 

[pr2-eugin]

I think in the past I explained to you that Round does not behave as expected. Not sure if you actually read through them (maybe not).

Round function in Access uses Banker's rounding. You need a much more sophisticated Rounding functions if every penny counts. Try checking out "Implementing a custom Rounding procedure"

 

[KenshiroUK]

Yes, I did read the post and I thought I might have fixed it to work, but I was wrong. Anyway I did finally fix it anyway.

 

[pr2-eugin]

Glad you have it sorted, it would help everyone if you could share the solution.

 

[CJ_London]

If rounding your VAT calculation these are the rules to apply. If comparing with a VAT invoice from a supplier you may occasionally find a penny difference due to the way they have calculated (see 17.5.1). You should store your calculated VAT within your table (and allow an edit in the purchase ledger) since the document must be reproduceable, even if the VAT rate changes.

17.5 Calculation of VAT on invoices – rounding of amounts ​

Note:

[FONT=Arial,Arial][FONT=Arial,Arial]The concession in this paragraph to round down amounts of VAT is designed for invoice traders and applies only where the VAT charged to customers and the VAT paid to HMRC is the same. As a general rule, the concession to round down is not appropriate to retailers, who should see paragraph 17.6. [/FONT][/FONT][FONT=Arial,Arial]

[FONT=Arial,Arial]You may round down the total VAT payable on all goods and services shown on a VAT invoice to a whole penny. You can ignore any fraction of a penny. [/FONT]
​

[/FONT]17.5.1 Calculation based on lines of goods or services
[FONT=Arial,Arial][FONT=Arial,Arial]If you wish to work out the VAT separately for a line of goods or services, which are included with other goods or services in the same invoice, you should calculate the separate amounts of VAT either by rounding: [/FONT]​

[/FONT]Page 80 of 222 Notice 700 The VAT Guide August 2013

• ​

​

[FONT=Arial,Arial][FONT=Arial,Arial]down to the nearest 0.1p - for example, 86.76p would be rounded down to 86.7p, or [/FONT][/FONT][FONT=Arial,Arial]

​

[/FONT][FONT=Arial,Arial][FONT=Arial,Arial]• ​

​

[/FONT][/FONT]

[FONT=Arial,Arial][FONT=Arial,Arial]to the nearest 1p or 0.5p - for example, 86.76p would be rounded up to 87p. [/FONT][/FONT][FONT=Arial,Arial]

[FONT=Arial,Arial]Whatever you decide, you must be consistent. [/FONT]
[FONT=Arial,Arial]The final total amount of VAT payable may be rounded down to the nearest whole penny. [/FONT]
​

[/FONT]17.5.2 Calculation based on tax per unit or per article
[FONT=Arial,Arial][FONT=Arial,Arial]If you want to work out the VAT per unit or per article (for example, for use in price lists), you must work out the amounts of VAT either to: [/FONT]
[/FONT][FONT=Arial,Arial][FONT=Arial,Arial]• ​

​

[/FONT][/FONT]

[FONT=Arial,Arial][FONT=Arial,Arial]4 digits after the decimal point and then round to 3 digits - for example, if the VAT is £0.0024, it should be rounded to £0.002 (0.2p), or [/FONT][/FONT][FONT=Arial,Arial]

​

[/FONT][FONT=Arial,Arial][FONT=Arial,Arial]• ​

​

[/FONT][/FONT]

[FONT=Arial,Arial][FONT=Arial,Arial]the nearest 1p or 0.5p. If you decide to do this, you must not round the VAT down to 'nil' on any unit or article that is liable at the standard or reduced rate - for example, if the VAT is £0.0024, it should be rounded to £0.005 (0.5p). [/FONT][/FONT][FONT=Arial,Arial]
[FONT=Arial,Arial]　[/FONT]

​

[/FONT]17.6 Calculation of VAT at retailers
[FONT=Arial,Arial][FONT=Arial,Arial]Most retailers account for VAT using a retail scheme. If that is the way you account for VAT, this paragraph does not affect you. [/FONT]​

[FONT=Arial,Arial]Retailers are increasingly using sophisticated till technology to identify the VAT due on each transaction and issue an invoice. If you do not use a retail scheme, but instead calculate VAT at line level or invoice level, you must not round the VAT figure [/FONT][/FONT]down[FONT=Arial,Arial][FONT=Arial,Arial]. However, you may round (up and down) each VAT calculation. [/FONT][/FONT]
[FONT=Arial,Arial][/FONT]
[FONT=Arial,Arial]
[/FONT]and you can get the full guide here
http://customs.hmrc.gov.uk/channels...yType=document&id=HMCE_CL_001596#P1359_106962
​