Currency calculation in a datasheet form

[Pure Salt]

I have a query which multiplies the tonnes (3 decimal points) by the cost (2 decimal points), this then displays the 'total cost'.

This works fine & rounds up/down correctly - until I add a 'sum' total field at the end of the datasheet form. It calculates the total cost using 3 decimal points & then converts to 2, which means it is sometimes a penny out.



Any ideas please how I can get it to calculate the total using 2 decimal points instead of 3?

[burrina]

Hi, take a look at this link, I think it is what you are looking for. http://allenbrowne.com/round.html

HTH

[Pure Salt]

Thanks, tried this but it rounds down. I need it to round up if it is .5 or above.

[burrina]

It also gave an example of Rounding up. Simply enter your criteria for the procedure.

If (Me.SomeValue) > .5 Then
'Do Something

[Pure Salt]

The rounding part works, it's the 'total' line on the datasheet form that calculates the other totals using 3 decimal places rather than the 2 is shows.

This is what it shows (The total of the 'total' column should be £102.58 - for eg if I click on the cell which shows £11.03 it then changes to £11.025):-

Customer	Rate	Tonnes	Total	With VAT	Month ID
	£12.00	0.50	£6.00	£7.20	7
	£12.00	1.58	£18.90	£22.68	7
	£12.00	0.63	£7.50	£9.00	7
	£12.00	1.38	£16.50	£19.80	7
	£12.00	1.25	£15.00	£18.00	7
	£6.00	4.61	£27.65	£33.18	7
	£9.00	1.23	£11.03	£13.23	7
		11.158	£102.57	£123.09

[burrina]

I can't see your code that does the calculation for the values! Here is an example that may help. Example is in query.

AmountEx: CCur(Nz(Round([tblTransDetail].[Quantity]*[tblTransDetail].[PriceEachEx],2),0))

[orange]

Have you tried using Currency data type?
This may help with some math issues in Access.

[Pure Salt]

Thanks, I have just tried your example - it does work but it is still rounding down.

My calculation is [Tonnes]*[Rate], so for eg 1.225 tonnes multiplied by £9 = £11.025, I need this to round up to £11.03 not down to £11.02

[Pure Salt]

I have used Round(CDbl([Tonnes]*[Rate]+0.000001),2) & it seems to have done the trick!