449,106 Members | 1,093 Online Need help? Post your question and get tips & solutions from a community of 449,106 IT Pros & Developers. It's quick & easy.

# When calculating time sheets/rates sum i get a rounding up value

 P: 8 Please help i am geting a rounding up error when trying to multiply one calculated field by another. Total Time `=Sum(([Time out]-[time in])*24)` Value = 24.34 Calculated Rates `=([price_material]*[quantity])/[Total Time]` Value = €10.68 Calculate Total `=[Calculated Rate]*[Total Time]` Value = €260.00 This should be €259.95 Oct 21 '10 #1
8 Replies

 100+ P: 393 Looks like the property of your total is set to "integer". Change it to "double" and recalculate. Oct 21 '10 #2

 P: 8 Thanks for yout prompt reply i tried the following. `=Format((CDbl([calculated rate]*[Total Time])),"#,##0.00 ")` sorrry but i am stoll getting €260.00 Oct 21 '10 #3

 100+ P: 393 `=Format((CDbl([calculated rate]*[Total Time])),"#,##0.00 ")` Is this in the control source of a form, in a query, in code? What is Cdbl? Oct 21 '10 #4

 P: 8 the code is in a field in a form upps! i was trying to format the firld for double i take it i got this wrong. Oct 21 '10 #5

 100+ P: 393 `=[calculated rate]*[Total Time]` This is all you should need if the property of the field that it's in is set to 'double'. Oct 21 '10 #6

 Expert Mod 15k+ P: 31,662 It looks like you have a field somewhere defined as an integer of some sort. Field Data Types are not so trivial they can simply be left to default. Double (Which is Data Type of Number and Field Size of Double) is not the best approach to use in your case I would suggest. It will be closer certainly, but explore the Data Type of Currency if you want the currency values to be stored absolutely correctly. In its simplest form, your problem is related to not considering this issue at the design stage of your database. It's your choice what you actually use, but you should at least give the matter consideration. Oct 21 '10 #7

 P: 8 Thanks for taking the time out to look at my project it would appear to have the correct field properties but i still cant stop the number from rounding up. Is it possible to set the property of a calculated (no table data source) field in a form to double? Time out = Date/Time Time in = Date/Time Material = Currency Rate = Currency Quantity = Number "Double" Oct 21 '10 #8

 Expert Mod 15k+ P: 31,662 I don't believe so Brittany. I think Access determines the type of field based on the calculations and which field types are used within the calculation. I can't see why your value would round to an integer value with the types you've just provided. Nothing there is integral, so there'd be no reason for it to. Oct 21 '10 #9 