1 cent problem

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

On Tue, 6 Sep 2005 17:01:12 +1200, "Bob" wrote:



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]

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


"John Vinson" wrote in message
...
On Tue, 6 Sep 2005 17:01:12 +1200, "Bob" wrote:



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]

On Wed, 7 Sep 2005 05:55:07 +1200, "Bob" wrote:

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]

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" wrote in message
...
On Wed, 7 Sep 2005 05:55:07 +1200, "Bob" wrote:

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]

On Wed, 7 Sep 2005 10:11:13 +1200, "Bob" wrote:

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

ditto

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]

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" wrote in message
...
On Wed, 7 Sep 2005 10:11:13 +1200, "Bob" wrote:

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

ditto

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]

On Wed, 7 Sep 2005 11:27:02 +1200, "Bob" wrote:

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]

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" wrote in message
...
On Wed, 7 Sep 2005 11:27:02 +1200, "Bob" wrote:

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]

On Wed, 7 Sep 2005 17:14:37 +1200, "Bob" wrote:

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]