globally running a rounding code

[Confusion]

Hi all, New here and reasonably new to coding so please excuse any errors!

I've been doing some searching and reading here as a result of a problem I have in an Access 97 database. Basically it's all based around money, figures and calculations. Lots and lots of them, only access doesn't seem to be able to make up it's mind whether to round up or down at any given moment, meaning 2 calculations using the same figures in different places are giving 2 different results, 1p out. When I subsequently run reports to the end user they get odd figures that dont quite add up!

I've found various versions of some rounding code for this problem here, but I have no idea which to use, or how to apply it to the database. There are so many different places calculations are run, in different forms, reports etc. How do I approach this? do I put the rounding code in every place that I need to round, or can I put it in one place and use it, if so how?!

Many thanks!

 

[FoFa]

It's not so much Access as it is the design of the calculations.
Example:
1.222 + 3.256 + 2.136 = 6.614
round(1.222,2) + round(3.256,2) + round(2.136,2) = 6.62
round(1.222 + 3.256 + 2.136,2) = 6.61

So, you see there can be a difference.
See the form example to verify.

 

[Confusion]

Thanks Fofa, never thought of it like that before!

OK, so taking that on board, I'm thinking the wisest thing to do would be to make all initial calculations that are permanantly writen to a field in a table rounded before they enter that table, so that all subsequent calculations are on the same basis. That does still lead me back to my original question though, How do I implement a function that's sat in the main macro code, in a seperate form/report? also what would be the best and simplest rounding code to use? there are several I've found on here but not really knowledgable to assess what I need!!!

Many Thanks

 

[Rich]

Create your own function and then use it like
=Round2CB([Field1]+[Field2])
where Round2CB is the function

 

[FoFa]

The odd thing about Access I noticed, is even if you round the values before storing them in the DB, sometimes it still puts more than 2 decimal places out there. (have not figured that out yet). If you set the table to display only 2 decimal places, it still stores the whole number, and will use it in any calculations. One thing I have had some success with, is basically creating a view of the table/s, but rounding the values as it pulls them from the table, then using that view as the basis for other things (like reports, etc.). Seems to vary as to what version of Access you are using however as to how well those things seem to work. About the only consistant way I have found to not create any rounding differances is to round the individule values when you pull them. You can try rounding them when you store them, but sometimes this is not a valid method as accountants, etc. like to see the original value.

 

[Confusion]

LOL - Rich, thanks for the vote of confidence, but my attempts at creating a round function have not been overly successful

FoFa

I'm OK as far as what is actually there, this is very much an in-house DB and the accountant is not worried about which way they round, as long as he stops having to look for 500 cases of 1 penny differences!!! I'll obviously have to pull the figure as is and work on it from there.

The annoying thing is, this has only just become apparent after a lot of use - basically all figures were (and are) input as whole pounds, but after an automated calculation has been applied which increases the value of the inputs by a certain percentage after a few months, the returned figures are running to several decimal places

 

[FoFa]

First thing I would try is to change your query that updates the values to update to rounded values instead. Then if it stores them correctly, you don't have to change anything else.
Another option might be to have a query that takes the unrounded values, rounds them and puts them back.

have you tried any of that?

 

[FoFa]

First thing I would try is to change your query that updates the values to update to rounded values instead. Then if it stores them correctly, you don't have to change anything else.
Another option might be to have a query that takes the unrounded values, rounds them and puts them back.

have you tried any of that?

 

[Rich]

Since any reporting etc. should be based on queries the problem's not to difficult to overcome, this is the only function I've found so far that has been consistently accurate enough to trust


Public Function Round2CB(Value, Optional Precision As Variant) As Double

If IsNull(Value) Then Exit Function
If IsMissing(Precision) Then Precision = 2
Value = Fix(Value * 10 ^ Precision + 0.5 * Sgn(Value)) / 10 ^ Precision
Round2CB = Value

End Function

I now use it everywhere even with the inbuilt Sum function

 

[Confusion]

Rich - many thanks for that code, LOL looking at that I can see why my attempts have not been tooooo successful

FoFa - that's what I was trying to attempt a while back, but I couldn't work out how to get the query to call the function, apply it and use the values. Maybe I was approaching it all wrong - wouldn't be the first time! I'm basically self taught through trial and error so I think I do a lot of things in a very topsy-turvy way!!!

LOL - just thought, maybe one of my rounding functions worked but I just implemented it wrong!

If you could use idiot speak to guide me how to use the function Rich has posted in the update query, when the function is stored in the main code modules it would be much appreciated - might show me where I am going wrong.

 

[Rich]

I wouldn't use an update query, I'm not convinced that the rounded value would be stored as such, or relied upon

 

[Confusion]

Just to let you guys know that I finally finished implementing all the rounding code in the couple of hundred places it was needed at about 2.30am

Big thank you to you both for your help. So far so good, all appears to be running smoothly - couple of minor glitches with passing from forms to reports I have to sort out but I'll post in the more relevant sections on that, just got to give it a "real world" test now!

Thanks again.