If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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. |
#13
|
|||
|
|||
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. |
#14
|
|||
|
|||
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] |
#15
|
|||
|
|||
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. |
#16
|
|||
|
|||
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 |
#17
|
|||
|
|||
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 |
#18
|
|||
|
|||
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. |
#19
|
|||
|
|||
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 |
#20
|
|||
|
|||
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. |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Strange problem with different networks. | Adam | General Discussion | 1 | March 13th, 2005 10:17 PM |
IE6 & Outlook Problem (Strange Problem)! | KW | Outlook Express | 4 | February 1st, 2005 08:31 AM |
Reinstalling OE... | KAR | Outlook Express | 24 | August 21st, 2004 06:52 PM |
Productkey problem when installing office 2003 on network | Stefan Schreurs | Setup, Installing & Configuration | 1 | June 1st, 2004 11:16 PM |
word error | mac | General Discussions | 1 | May 6th, 2004 08:14 AM |