Rounding of numbers?

[Compressor]

I know I have read a topic which handles this... there was a piece of code in it too which makes access round numbers correctly up or down to the second decimal based on the third. Access seems to do this a bit strange so small differences occur when calculating with currency for example. But I can't seem to find that topic anymore. It is called "rounding numbers" in english right? Can anyone help me with this please?

 

[KenHigg]

See if this helps:

Link

 

[Compressor]

Ah, yes! Thank you very much! It looks like this will do the trick.

 

[Compressor]

Well... indeed it looked like it would do the trick. Having applied it to all fields (currency, decimals = auto) it still makes calculation errors.

First I will give the "on screen" example, then the actual, underlying numbers:
*****************
Total Ex VAT = 11,74
VAT 19% = 1,74
Total In VAT = 13,49
*****************
Total Ex VAT = 11,7441
VAT 19% = 1,7421
Total In VAT = 13,4862
*****************
How can I make it so it rounds those number correctly? I'm really at a loss now... Can anyone help?

 

[KenHigg]

Pat and / or Doc are good with this issue - Maybe they'll take a peek when they come through...

 

[RuralGuy]

See if this link helps: Implementing a custom Rounding procedure

 

[Cosmos75]

Here's my two cents
- VBA Rounding Functions

 

[Rich]

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

 

[Bat17]

rounding would appear to be the least of your problems

Total Ex VAT = 11,74
VAT 19% = 1,74

I make vat there to be more like 2,23!

Peter

 

[gemma-the-husky]

the custom rounding takes a real (double) number and converts it as follows

real number * 100 (say) eg 17.74123 becomes 1774.123

convert to long to remove decimal parts becomes 1774
(i think this truncates hence the addition of 0.5 in the example to deal with rounding up/down issues)

now divide by 100 again so 1774 finally becomes 17.74

-------------
ignoring the actual calculation errors noted above (perhaps its not using 19% - looks more like 15% to me)

however it seems to me that this doesn't necessarily solve the problem exactly, although its probably ok practically, and you should be careful before trying to use the rounded value as an exact value in some cases.

Since some decimals cannot be represented with binary expansion, perhaps a "rounded" value of 17.74 may still be ACTUALLY stored as eg 17.7400000001

 

[Compressor]

Indeed.... The rounding right now is the least of my problems.... I'll fix that after the calculation error then. The code I use is:

Private Sub PricePerOneExBTW_LostFocus()
        
        If Nz(Me.Parent!SalesBTWTarief, 19) = 19 Then
            PricePerOneBTWAmount.Value = PricePerOneExBTW * 0.19
        ElseIf Nz(Me.Parent!SalesBTWTarief, 6) = 6 Then
            PricePerOneBTWAmount.Value = PricePerOneExBTW * 0.06
        ElseIf Nz(Me.Parent!SalesBTWTarief, 0) = 0 Then
            PricePerOneBTWAmount.Value = PricePerOneExBTW * 0
        End If
    
    PricePerOneInBTW.Value = PricePerOneExBTW.Value + PricePerOneBTWAmount.Value
    PriceForTotalNrOfThisItemInBTW.Value = PricePerOneInBTW.Value * TotalNrOfThisItem
    PriceForTotalNrOfThisItemBTWAmount.Value = PricePerOneBTWAmount.Value * TotalNrOfThisItem
    PriceForTotalNrOfThisItemExBTW.Value = PricePerOneExBTW.Value * TotalNrOfThisItem
    
End Sub

**********************************************
Private Sub PricePerOneInBTW_LostFocus()
        
        If Nz(Me.Parent!SalesBTWTarief, 19) = 19 Then
            PricePerOneBTWAmount.Value = PricePerOneInBTW / 119 * 19
        ElseIf Nz(Me.Parent!SalesBTWTarief, 6) = 6 Then
            PricePerOneBTWAmount.Value = PricePerOneInBTW / 106 * 6
        ElseIf Nz(Me.Parent!SalesBTWTarief, 0) = 0 Then
            PricePerOneBTWAmount.Value = PricePerOneInBTW / 100 * 0
        End If
    
    PricePerOneExBTW.Value = PricePerOneInBTW.Value - PricePerOneBTWAmount.Value
    PriceForTotalNrOfThisItemExBTW.Value = PricePerOneExBTW.Value * TotalNrOfThisItem
    PriceForTotalNrOfThisItemBTWAmount.Value = PricePerOneBTWAmount.Value * TotalNrOfThisItem
    PriceForTotalNrOfThisItemInBTW.Value = PricePerOneInBTW.Value * TotalNrOfThisItem
    

End Sub

Apart from whether I should have used a different If Then construction (I need to choose the VAT percentage before entering data into the subform based on that the calc has to be done)... what is wrong with this calculation?



There are some strange other things happening too...
The above piece of code is used in a contiunous form for the respective fields. There are three more fields, which just multiply the outcome of the above times the amount of items someone wants to buy.

Also in the footer I have placed three text boxes:
SumTotalForFooterInBtw -> containing -> =Sum([PriceForTotalNrOfThisItemInBTW]) as control source and Round([SumTotalForFooterInBTW],2) in the afterupdate event

SumTotalForFooterExBtw -> containing -> =Sum([PriceForTotalNrOfThisItemExBTW]) as control source and Round([SumTotalForFooterExBTW],2) in the afterupdate event

SumTotalForFooterBtwAmount -> containing -> =Sum([PriceForTotalNrOfThisItemBTWAmount]) as control source and Round([SumTotalForFooterBTWAmount],2) in the afterupdate event

Now... I use:

Private Sub ProductName_GotFocus()
    Me.Parent!SalesTotalExBTW.Value = Me.SumTotalForFooterExBtw
    Me.Parent!SalesTotalBTWAmount.Value = Me.SumTotalForFooterBtwAmount
    Me.Parent!SalesTotalInBTW.Value = Me.SumTotalForFooterInBtw
End Sub

To get the value in the footer textboxes stored in the appropriate tablefields.

But although the calculation is done in the non-bound textboxes in the footer of the continuous subform, it doesn't get "put through" into the fields on the parent form until I hit a field containing the same code again:

Me.Parent!SalesTotalExBTW.Value = Me.SumTotalForFooterExBtw
    Me.Parent!SalesTotalBTWAmount.Value = Me.SumTotalForFooterBtwAmount
    Me.Parent!SalesTotalInBTW.Value = Me.SumTotalForFooterInBtw
End Sub

I've used that code on several fields on the got focus and lost focus events, but the calculation in the footer won't happen until the record has been completed and the cursor moves to the next, empty, record. And then a field
containing the above code needs to "get hit" to get the code into the corresponding fields on the parent form which will be stored in the table.

So three problems.. from bad to worse to even worse I think... The calculation error. The rounding error. The thing where I have to tab through to much times until I hit a field with the above piece of code in it. It would be nice if all that would be done the moment the record is completed. I've really been trying the last month to get where I am now, but... I'm stuck on this part. Sorry... I really need some help again :$

 

[liddlem]

While you're on the subject - (I dont want to hijack this thread)
I had an interesting issue today where a client has a NUMBER field size was "Long Integer" and the format was standard. Decimal placed set to Zero.
Database was created in 2003 - I think using Access 2000?

They wanted to show 2 decimal places, so I changed Decimal places to 2.
No problem - 2 decimal places are displayed.

However, when I typed in a number like 23.21 - The value was rounded UP? and the field displayed as 24.00
After trying all the different options, I found that I HAD to set the field size to DOUBLE in order to display the value correctly.

Is this problem directly related to the one posed in this thread?
Does anybody know why the other field size types (Long Integer, Short Integer, etc) would not display the decimal values?

Thanks for letting me chip in here.

 

[Rich]

Look up the definition of the Integer data type

 

[liddlem]

Thanks Rich
I guess that WAS a really THICK question.
For some reason I got my data types completly muddled today.
I was totally convinced by my minds lie - I'm actually embarrased!
Thanks

 

[Compressor]

I really can't find anything wrong with the above calculation..... The number I put up in this topic must have gotten scrambled by the way I have set up the form/calculation. I HAVE to select the amount of btw immediately correctly and then enter the numbers. If I don't do that and change the BTW (VAT) percentage later on, I of course, must cycle through all the fields again to get the correct value calculated. The records I used were kinda messed up in that way I guess. So unless anyone thinks the calculus is wrong in the code in this one (placed in a post of mine from last evening) I'm going to go back to the rounding thing today. When that's taken care of... well... the "cycling through all fields" part will come at a later time. I need to get started on creating queries and reports too for our beloved tax reports and to be able to book things from this database into the bookkeeping program I'm going to use

I'm still a bit puzzled about your reply gemmathehusky, but I'm sure when I sit down and really take a look at it I must be able to figure it out

Thanks again!
I'll be able to work on the rouding thing later this afternoon again.

 

[Rich]

You're making several mistakes especially if this is supposed to be a book keeping db. You should not be storing calculated values, use a query to display totals. You only need to store the netcost, quantity and vat rate applicable to the item.

 

[gemma-the-husky]

which bit?

all i was saying is that there is an potential error in any real number, because everything is a binary representation of a real number. any integral number can be represented as a binary number, but NOT every real number.

so
0.1 (binary) is 0.5 decimal
0.01 is 0.25 decimal
0.11 is 0.75 decimal
0.001 is 0.125 decimal
0.0001 os 0.0625 decimal etc

there is therefore a finite set of real numbers that can be exactly represented by a binary expansion, and a much larger infinite set of real numbers that cannot be.

eg 0.5 decimal can clearly be stored exactly
but 0.1 may or may not be able to be stored precisely. it may ACTUALLY be stored as 0.100000000001 (say)

in the same way as fraction of 1/9 cannot be exactly represented in a finite decimal expansion. its 0.111111111 recurring

you therefore have to be somewhat careful with handling real numbers - eg you need to be careful before carrying out equality tests, although as i say this is most likely to be a theoretical observation, rather than a pratical issue.

 

[Compressor]

You're making several mistakes especially if this is supposed to be a book keeping db. You should not be storing calculated values, use a query to display totals. You only need to store the netcost, quantity and vat rate applicable to the item.

Which mistakes?
The formula's are correct right? 19% is the VAT. So if I have the price of an amount ex VAT which is 10 euro which is 100%, then the price including VAT is 119% since the VAT is 19% right? And the reverse is also true for when I only have the amount of a product Including VAT. Am I missing something?

And yes, I know I'm trespassing on the whole "store the totals" bit when it comes to normalization. What's so bad about it? The numbers are stored in the DB. They don't change anymore, ever. But I need to be able to pull them up quickly, and they need to be stored per invoice according to dutch law. Otherwise I will need to keep paper copies of all invoices, which is what I'm trying to avoid here.

And no, its not supposed to be a bookkeeping DB. It is a client DB which includes the client data, case data, appointment data, and invoice data/products sold /services rendered per customer. The invoicing is done by access as I can make it per my needs, and the actual bookkeeping is done in another piece of software. So... it would be nice if the numbers in access round the same as in the bookkeeping software which is.... based on a database as well. So... Anyway, please remember... I am no programmer nor a professional with access. I'm just trying to get through the first year, maybe two of starting up my company and making some money. When that goes ok, I can afford to buy or maybe let someone make a more professional solution.

I'm stumped by the fact that the rounding of financial numbers in a product such as access isn't a... how would you say that in english? "program native functionality". Especially since there is a seperate type, namely currency and even euro, that you can assign to your field in the DB. My non-programmer mind says that it should be doing that automatically then, especially sincethe currency type of data is chosen. But that's just me...

It is a one user - one computer - one man company database. So... yeah, I'm storing the numbers in a table. Why not? Storage space is dirt cheap nowadays. And isn't it true that in enterprise environments, when the numbers do get stored, the server(s) CPU is massively "lightened" when it doesn't have to do all those calculations time and time again for hundreds of people and thousands of records? In my case that isn't true, since it is a one man-one computer -few clients DB. But.... I'm just wondering. I'm guessing that that is done for development purposes?

It doesn't look like I will be reaching the 2GB limit anytime soon and when that does happen in a short time.... well... then the business will be going well and a more professional solution can be bought. So what is the downside of storing them in my case? I'm not trying to be a wise-*ss or something, but I'm really wondering about that.

I'm gonna try and see how it works out when using a query but I'm afraid I'm just not skilled enough to make it do what it does in the form now by using a query. I'm limited by my skills. But... since I do like to learn I'm gonna fight with queries now.... just to see how it works and how it can be applied. I really like that you guys are pointing out of my flaws in this one so I can improve and learn.


@gemma: ok, now i understand what you meant with your post. But still... I human see that when a number is like 0,111111111111 etc. For money calculations is normally truncated and then rouned up or down. 2 is down, 3 is up etc. I'm still stumped as to why it isn't a native access function. But I will try the piece of code that is linked to on the previous page. The "banking" rounding of numbers... I hope that will be kinda easy to implement for me.

Edit: never mind trying to explain the "why not store but use query" thing to me ;-) There are probably thousands of examples readily available to you guys which I can't even start to think of... ;-)

Edit2: @Rich: Hey thanks man... I didn't even see the function you put up on the previous page until now... I'm ashamed.... I'll try that one first right now. Thanks!

 

[Bat17]

not sure about the rest of it but you should be able to simplify the first if with
PricePerOneBTWAmount.Value = PricePerOneExBTW * Nz(Me.Parent!SalesBTWTarief, 19) / 100

this will default to 19 for null otherwise use whats in the box

HTH

Peter

 

[Compressor]

Ok, I'll try that. Thanks

I'm running into another (strange or logical?) thing. I've made a function module using the code from Rich:

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

and placed it in the afterupdate event fields of all the fields that hold currency. When I tab through the fields now, there is no problem. But when I use enter when I've typed in a number into a new record to goto the next field an error comes up:

CCS Can't find the macro 'Round2CB(PricePerOneExBTW, 2)' The macro or its macro group doesn't exist, or the macro is new but hasn't been saved. Note that when you enter te macrogroupname.macroname syntax in an argument, you must specify the name the macro's macro group was last saved under.

Is this something that was/is to ben expected and should I use tab to move to the new field? Or is there something wrong?

And, just to be on the save side, also in this case it is like gemma said? when I have typed some values, for example ex VAT = 11,74 --- in VAT = 13,97 --- VAT amount = 2,23
and then place the cursor in the field for say, VAT amount, the value displayed is 2,2306. Is it correct that the value "underneath" the 2,23 is still displayed this way? Since I used the round (or round2cb in this case) function to round the values... It still gets stored as what it really is... so that is what is used during calculations right? So when for example the VAT amount would double, the value would be 4,4612, but is rounded to 4,46 in the display only?