1 cent problem

[Bob]

I have a database that creates Invoice/Statements but some times even if
client owes $164.68 and I put in that amount for payment I get a (0.01) , So
I make the payment $164.679 and it comes up 0.00 I have default 2 decimals
points but it still lets me 3 or more numbers under decimal point, any help
please

Thanks in advance.........Bob Vance

 

[John Vinson]

I have a database that creates Invoice/Statements but some times even if
client owes $164.68 and I put in that amount for payment I get a (0.01) , So
I make the payment $164.679 and it comes up 0.00 I have default 2 decimals
points but it still lets me 3 or more numbers under decimal point, any help
please

Thanks in advance.........Bob Vance

A Currency datatype has four and exactly four decimal places (no
matter what the decimal setting or the Format say, they're just for
display). My guess is that you're doing some calculations (taxes
perhaps?) involving multiplication or division, which are leaving
values in the third and fourth decimals.

The solution is to wrap each such expression in a call to the Round()
function, e.g.

SalesTax: Round([TotalCost] * [TaxRate], 2)

or whatever is the expression in your query.

John W. Vinson[MVP]

 

[Bob]

Thanks John, yes each Invoice has a tax rate of *12.5% plus some invoices
are divided by a percentage that the client has in the ownership, is there
some way I can Round the Invoice total to the nearest cent...Thanx Bob

I have a database that creates Invoice/Statements but some times even if
client owes $164.68 and I put in that amount for payment I get a (0.01) ,
So
I make the payment $164.679 and it comes up 0.00 I have default 2
decimals
points but it still lets me 3 or more numbers under decimal point, any
help
please

Thanks in advance.........Bob Vance

A Currency datatype has four and exactly four decimal places (no
matter what the decimal setting or the Format say, they're just for
display). My guess is that you're doing some calculations (taxes
perhaps?) involving multiplication or division, which are leaving
values in the third and fourth decimals.

The solution is to wrap each such expression in a call to the Round()
function, e.g.

SalesTax: Round([TotalCost] * [TaxRate], 2)

or whatever is the expression in your query.

John W. Vinson[MVP]

 

[John Vinson]

Thanks John, yes each Invoice has a tax rate of *12.5% plus some invoices
are divided by a percentage that the client has in the ownership, is there
some way I can Round the Invoice total to the nearest cent...Thanx Bob

Yes; and I explained how to do that in my previous post. Did you try
it?

Just take the expressoin which does the calculation - you didn't post
it, so I can't use your actual example - and wrap it in a call to the
Round function:

Round(<your expression here>, 2)


John W. Vinson[MVP]

 

[Bob]

Thanks , Would any of this script have something to do with the calculation:

Public Sub SubCalculate()
Dim dblSubTotal As Double, dblTotalAmount As Double
Dim dblWithoutDailyAmount As Double
Dim dblMonthlyChargeAmount As Double, dblAdditionChargeAmount As Double

If tbDailyChargeAmount1.value = "" Or IsNull(tbDailyChargeAmount1.value)
Then
dblWithDailyChargeAmount1 = 0
Else
dblWithDailyChargeAmount1 = CDbl(tbDailyChargeAmount1.value)
End If

If tbDailyChargeAmount2.value = "" Or IsNull(tbDailyChargeAmount2.value)
Then
dblWithDailyChargeAmount2 = 0
Else
dblWithDailyChargeAmount2 = CDbl(tbDailyChargeAmount2.value)
End If

If tbDailyChargeAmount3.value = "" Or IsNull(tbDailyChargeAmount3.value)
Then
dblWithDailyChargeAmount3 = 0
Else
dblWithDailyChargeAmount3 = CDbl(tbDailyChargeAmount3.value)
End If

dblMonthlyChargeAmount = Nz(DSum("MonthlyChargeAmount ",
"TmpMonthlyCharge"), 0)
dblAdditionChargeAmount = Nz(DSum("AdditionChargeAmount",
"TmpAdditionCharge"), 0)

dblSubTotal = dblMonthlyChargeAmount + dblAdditionChargeAmount +
dblWithDailyChargeAmount1 + dblWithDailyChargeAmount2 +
dblWithDailyChargeAmount3
dblWithoutDailyAmount = dblMonthlyChargeAmount + dblAdditionChargeAmount

tbSubTotal.value = dblSubTotal

If Len([cbGSTOptions]) = 0 Then
dblGSTOptionsValue = 0
dblTotalAmount = dblGSTOptionsValue + dblSubTotal
tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount
Exit Sub
End If

Dim recGSTOptions As New ADODB.Recordset, sngGstPercentage As Single

recGSTOptions.Open "SELECT * FROM tblGSTOptions WHERE GSTOptionsText
LIKE '" _
& cbGSTOptions.value & "'", cnnStableAccount, adOpenDynamic,
adLockOptimistic

If recGSTOptions.EOF = True And recGSTOptions.BOF = True Then
dblGSTOptionsValue = 0
dblTotalAmount = dblGSTOptionsValue + dblSubTotal
tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount
MsgBox "Invalid GSTOption.", vbApplicationModal + vbInformation +
vbOKOnly
Exit Sub
End If
sngGstPercentage = CSng(Nz(recGSTOptions.Fields("GSTPercentage"), 0))

If recGSTOptions.Fields("ynIncludeDaily") = True Then
dblGSTOptionsValue = (dblSubTotal * sngGstPercentage)
Else
dblGSTOptionsValue = (dblWithoutDailyAmount * sngGstPercentage)
End If
dblTotalAmount = dblGSTOptionsValue + dblSubTotal

tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount

Set recGSTOptions = Nothing
End Sub

Private Sub tbWithoutGST_AfterUpdate()
'Me.tbWithGST = Me.tbWithoutGST / (1 + (Me.tbRate / 100))
'15/05/05 Mohan
Me.tbWithGST = Me.tbWithoutGST / (1 + (Me.tbRate))
End Sub
Public Function TwoDigit(val As Currency) As Currency

Dim tempVal As Currency
tempVal = val * 100
tempVal = tempVal \ 1 'keep just the 'integer portion'
TwoDigit = tempVal / 100 'now divide and will have only two digit accuracy
'returned.

End Function

 

[John Vinson]

Thanks , Would any of this script have something to do with the calculation:

Ummm... yes. I take it that someone else wrote the code, and you're
trying to figure out what it does?

First off, if the calculated values are (as it appears) money amounts,
I would VERY strongly recommend changing your table design to store
them in Currency fields, rather than Number... Double; and change all
the As Double DIM statements to As Currency. Double values have up to
14 decimal places precision but they do suffer from roundoff error;
for instance the number 0.1 is an infinite repeating binary fraction,
meaning that 0.1 + 0.1 + 0.1 is NOT exactly equal to 0.3.

That said - note my changes suggested below.

Public Sub SubCalculate()
Dim dblSubTotal As Double, dblTotalAmount As Double
Dim dblWithoutDailyAmount As Double
Dim dblMonthlyChargeAmount As Double, dblAdditionChargeAmount As Double

If tbDailyChargeAmount1.value = "" Or IsNull(tbDailyChargeAmount1.value)
Then
dblWithDailyChargeAmount1 = 0
Else
dblWithDailyChargeAmount1 = CDbl(tbDailyChargeAmount1.value)
End If

Replace the five lines above with

dblWithDailyChargeAmount1 = NZ(tbDailyChargeAmount1)

If tbDailyChargeAmount2.value = "" Or IsNull(tbDailyChargeAmount2.value)
Then
dblWithDailyChargeAmount2 = 0
Else
dblWithDailyChargeAmount2 = CDbl(tbDailyChargeAmount2.value)
End If

dbWithDailyChargeAmount2 = NZ(tbDailyChargeAmount2)

If tbDailyChargeAmount3.value = "" Or IsNull(tbDailyChargeAmount3.value)
Then
dblWithDailyChargeAmount3 = 0
Else
dblWithDailyChargeAmount3 = CDbl(tbDailyChargeAmount3.value)
End If

dblMonthlyChargeAmount = Nz(DSum("MonthlyChargeAmount ",
"TmpMonthlyCharge"), 0)

dblMonthlyChargeAmount = Round(Nz(DSum("MonthlyChargeAmount ",
"TmpMonthlyCharge"), 0),2)

dblAdditionChargeAmount = Nz(DSum("AdditionChargeAmount",
"TmpAdditionCharge"), 0)

dblAdditionChargeAmount = Round(Nz(DSum("AdditionChargeAmount",
"TmpAdditionCharge"), 0),2)

dblSubTotal = dblMonthlyChargeAmount + dblAdditionChargeAmount +
dblWithDailyChargeAmount1 + dblWithDailyChargeAmount2 +
dblWithDailyChargeAmount3

dblSubTotal = Round(dblMonthlyChargeAmount +
dblAdditionChargeAmount +
dblWithDailyChargeAmount1 + dblWithDailyChargeAmount2 +
dblWithDailyChargeAmount3, 2)


and so on, and so on; when you have a value being calculated,
especially if the calculation involves a multiplication or a division,
use the Round() function to round the result to two decimal places.

And DO change the datatype. It'll be a good deal of work, changing it
in the tables and changing all the variables using the field to
Currency, but you'll be a lot safer from subtle errors.

John W. Vinson[MVP]

 

[Bob]

Im sorry I am lost "Replace the 5 lines above with" Im not sure what I have
to do replace all 5 lines with what. There seems to be six lines above your
text...Thanks Bob

 

[John Vinson]

Im sorry I am lost "Replace the 5 lines above with" Im not sure what I have
to do replace all 5 lines with what. There seems to be six lines above your
text...Thanks Bob

That's because your newsreader wrapped the lines. It's five lines in
your original code - don't copy and paste, because word wrap will
confuse the VBA compiler!

If you don't understand what the code is doing, and just blindly make
changes, you may get into trouble. Is the code in fact from someone
else? Is there someone comfortable with VBA coding who can help? I'd
correct the whole code for you but that's going a bit beyond the level
of free volunteer support I fear... especially if it involves changing
the datatype.


John W. Vinson[MVP]

 

[Bob]

Yes somebody else wrote it for me , I don't mind trying myself as I will
have a copy backup if I get into trouble,
If you can explain how to change the datatype as well I will try, Thanks Bob

 

[John Vinson]

Yes somebody else wrote it for me , I don't mind trying myself as I will
have a copy backup if I get into trouble,
If you can explain how to change the datatype as well I will try, Thanks Bob

I'd say add the code fixes first: the datatype is indeed something
you'll want to do, but at a guess it's not *essential*. You may get
some cases where the program shows a balance due of $0.00 and it won't
go away (since it's actually a balance due of $0.0000000000000087
which is nonzero).

How to change it depends entirely on what needs to be changed. Since I
don't know where else these fields are used, or how, I cannot suggest
how that might be done. Again, I can't justify doing all your work for
you and walking you through step by step on a free volunteer basis.

John W. Vinson[MVP]

 

[Bob]

Thanks John, I changed the code and did not get any errors, so I suppose it
is working , will this stop the 00.1 error I get sometimes?.....Thanks again
Bob

Public Sub SubCalculate()
Dim dblSubTotal As Double, dblTotalAmount As Double
Dim dblWithoutDailyAmount As Double
Dim dblMonthlyChargeAmount As Double, dblAdditionChargeAmount As Double

If tbDailyChargeAmount1.value = "" Or IsNull(tbDailyChargeAmount1.value)
Then
dblWithDailyChargeAmount1 = 0
Else
dblWithDailyChargeAmount1 = Nz(tbDailyChargeAmount1)
End If

If tbDailyChargeAmount2.value = "" Or IsNull(tbDailyChargeAmount2.value)
Then
dblWithDailyChargeAmount2 = 0
Else
dblWithDailyChargeAmount2 = Nz(tbDailyChargeAmount2)
End If

If tbDailyChargeAmount3.value = "" Or IsNull(tbDailyChargeAmount3.value)
Then
dblWithDailyChargeAmount3 = 0
Else
dblWithDailyChargeAmount3 = Nz(tbDailyChargeAmount3)
End If


dblMonthlyChargeAmount = Round(Nz(DSum("MonthlyChargeAmount ",
"TmpMonthlyCharge"), 0), 2)

dblAdditionChargeAmount = Round(Nz(DSum("AdditionChargeAmount",
"TmpAdditionCharge"), 0), 2)

dblSubTotal = Round(dblMonthlyChargeAmount + dblAdditionChargeAmount +
dblWithDailyChargeAmount1 + dblWithDailyChargeAmount2 +
dblWithDailyChargeAmount3, 2)
dblWithoutDailyAmount = dblMonthlyChargeAmount + dblAdditionChargeAmount

tbSubTotal.value = dblSubTotal

If Len([cbGSTOptions]) = 0 Then
dblGSTOptionsValue = 0
dblTotalAmount = dblGSTOptionsValue + dblSubTotal
tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount
Exit Sub
End If

Dim recGSTOptions As New ADODB.Recordset, sngGstPercentage As Single

recGSTOptions.Open "SELECT * FROM tblGSTOptions WHERE GSTOptionsText
LIKE '" _
& cbGSTOptions.value & "'", cnnStableAccount, adOpenDynamic,
adLockOptimistic

If recGSTOptions.EOF = True And recGSTOptions.BOF = True Then
dblGSTOptionsValue = 0
dblTotalAmount = dblGSTOptionsValue + dblSubTotal
tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount
MsgBox "Invalid GSTOption.", vbApplicationModal + vbInformation +
vbOKOnly
Exit Sub
End If
sngGstPercentage = CSng(Nz(recGSTOptions.Fields("GSTPercentage"), 0))

If recGSTOptions.Fields("ynIncludeDaily") = True Then
dblGSTOptionsValue = (dblSubTotal * sngGstPercentage)
Else
dblGSTOptionsValue = (dblWithoutDailyAmount * sngGstPercentage)
End If
dblTotalAmount = dblGSTOptionsValue + dblSubTotal

tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount

Set recGSTOptions = Nothing
End Sub

 

[Vincent Johns]

I'd say add the code fixes first: the datatype is indeed something
you'll want to do, but at a guess it's not *essential*. You may get
some cases where the program shows a balance due of $0.00 and it won't
go away (since it's actually a balance due of $0.0000000000000087
which is nonzero).

How to change it depends entirely on what needs to be changed. Since I
don't know where else these fields are used, or how, I cannot suggest
how that might be done. Again, I can't justify doing all your work for
you and walking you through step by step on a free volunteer basis.

John W. Vinson[MVP]

Let me add a note of caution here -- with money, since rounding takes
place at different times over a sequence of transactions, there is NO
GUARANTEE that the rounded total will always equal the sum of its parts.
For example, you might have 3 people who need to equally split a
$10.00 restaurant tip. You need to decide, or discuss with your
customers/clients, exactly what to do in such situations, or maybe
there's some law that decides for you. In any case, your calculations
will have to match the rules that you (and others) have agreed to use in
your accounting system. And using the "Currency" data type takes care
of a lot of the headaches.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Bob]

With my Invoice system the 10.00 goes to 3 people at 33.3 so I know I am
missing 0.1% but I accept that...Thanks Bob

 

[John Vinson]

Thanks John, I changed the code and did not get any errors, so I suppose it
is working , will this stop the 00.1 error I get sometimes?.....Thanks again
Bob

Keep going.

I was not providing the COMPLETE SOLUTION. I was providing *AN
EXAMPLE*, using Round() on the first few expressions.

You need to use Round() on *all* the expressions which might return a
noninteger value, in particular any which involve multiplication. Down
the listing a ways you have

If recGSTOptions.Fields("ynIncludeDaily") = True Then
dblGSTOptionsValue = (dblSubTotal * sngGstPercentage)
Else
dblGSTOptionsValue = (dblWithoutDailyAmount *
sngGstPercentage)
End If
dblTotalAmount = dblGSTOptionsValue + dblSubTotal

Both calculations of dblGSTOptionsValue should be rounded to the
nearest penny, using the Round() function. Do you see what I'm getting
at? Yon need to *read and understand* the code, and apply the Round()
function where it's necessary based on understanding the code - not
just blindly copy examples. It's after midnight here and I'm too
sleepy to do it well, or maybe I'd just do it for you - but maybe it's
better that you stop, study, think, and work through it yourself, so
you'll be able to maintain this in the future.

John W. Vinson[MVP]

 

[Vincent Johns]

With my Invoice system the 10.00 goes to 3 people at 33.3 so I know I am
missing 0.1% but I accept that...Thanks Bob

That's OK -- my point was that everyone affected by your calculations
should be aware of what happens, and apparently you're taking care of that.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Bob]

Thanks Bob Vance

Do I have to Round() This? dblGSTOptionsValue = 0

If recGSTOptions.EOF = True And recGSTOptions.BOF = True Then
dblGSTOptionsValue = 0
dblTotalAmount = Round(dblGSTOptionsValue + dblSubTotal)
tbGSTOptionsValue.value = Round(dblGSTOptionsValue)
tbTotalAmount.value = Round(dblTotalAmount)
MsgBox "Invalid GSTOption.", vbApplicationModal + vbInformation +
vbOKOnly
Exit Sub
End If
sngGstPercentage = CSng(Nz(recGSTOptions.Fields("GSTPercentage"), 0))

If recGSTOptions.Fields("ynIncludeDaily") = True Then
dblGSTOptionsValue = Round((dblSubTotal * sngGstPercentage))
Else
dblGSTOptionsValue = Round((dblWithoutDailyAmount *
sngGstPercentage))
End If
dblTotalAmount = Round(dblGSTOptionsValue + dblSubTotal)

tbGSTOptionsValue.value = Round(dblGSTOptionsValue)
tbTotalAmount.value = Round(dblTotalAmount)

Set recGSTOptions = Nothing

 

[Bob]

I also have this in my code, would this help? Thanks Bob
Public Function TwoDigit(val As Currency) As Currency

Dim tempVal As Currency
tempVal = val * 100
tempVal = tempVal \ 1 'keep just the 'integer portion'
TwoDigit = tempVal / 100 'now divide and will have only two digit accuracy
'returned.

End Function

 

[Vincent Johns]

I also have this in my code, would this help? Thanks Bob
Public Function TwoDigit(val As Currency) As Currency

Dim tempVal As Currency
tempVal = val * 100
tempVal = tempVal \ 1 'keep just the 'integer portion'
TwoDigit = tempVal / 100 'now divide and will have only two digit accuracy
'returned.

End Function

It's more customary to do unbiased rounding, so that $5.948 gets rounded
to $5.95 instead of $5.94 the way your code would round it.

You could try this:

tempVal = (val * 100) + 0.5

in the first tempVal line, or you could replace most of your code with

TwoDigit = Round(val, 2)

I don't have the reference in front of me right now, but for numbers on
the cusp, I think it rounds to the nearest even number:

Round(3.449,1) = 3.4
Round(3.450,1) = 3.4
Round(3.451,1) = 3.5

but

Round(3.549,1) = 3.5
Round(3.550,1) = 3.6


-- Vincent Johns ([email protected])
Please feel free to quote anything I say here.

 

[Bob]

This is what my coder told me is he correct:
"You have rounded each value of the calculation which will reduce the
accuracy of the calculation. So I have rounded only Totals."

Thanks Bob

 

[Vincent Johns]

This is what my coder told me is he correct:
"You have rounded each value of the calculation which will reduce the
accuracy of the calculation. So I have rounded only Totals."

Thanks Bob

(See my message dated 9/8/2005, 16:10 UTC)

There is, in general, no consistent way to round fractional currencies
so that the rounded total is the total of the rounded amounts.

You can come close, but as I mentioned, you'll have to formulate rules
about exactly when you do rounding, and what kind of rounding you do.
For example, in a bank account, calculate interest and round to the
nearest $0.0001 at the end of each month (or day, or whatever), and when
a withdrawal occurs or you publish an account statement, then you round
to the nearest $0.01. If you set rules like this and do the same for
all customers/suppliers/employees/clients/&c. then nobody will have a
valid basis for thinking you're mistreating him.

But I suggest that you NOT do it randomly. Using "Currency" data type
will let you do normal (GAAP, I assume) styles of calculation without
having to put much effort into them. I'm no expert here, but I'd guess
that your main source of trouble will be in handling interest payments
or proportional distributions where the rules aren't clearly stated.
You might want to ask an accountant for advice on this. (This kind of
problem has been around a lot longer than computers have been!)


-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.