1 cent problem

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

John Vinson wrote:

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]

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
Please feel free to quote anything I say here.

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

"Vincent Johns" wrote in message
ink.net...
John Vinson wrote:

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]

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
Please feel free to quote anything I say here.

On Thu, 8 Sep 2005 10:32:32 +1200, "Bob" wrote:

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]

Bob wrote:

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
Please feel free to quote anything I say here.

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

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

Bob wrote:

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 )
Please feel free to quote anything I say here.

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

Bob wrote:
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
Please feel free to quote anything I say here.