Calculated Totals not adding up
- Thread starter ddm
- Start date
CJ... here goes:
WorkloadDate Hours Rate ExtendedTotal
8/20/2013 4.00 $10.88 $43.52
8/20/2013 4.00 $10.88 $43.52
8/20/2013 3.75 $10.88 $40.80
8/20/2013 4.00 $10.88 $43.52
8/20/2013 4.50 $10.77 $48.47
8/20/2013 4.50 $10.77 $48.47
8/20/2013 7.00 $10.77 $75.39
8/20/2013 6.50 $10.77 $70.01
8/20/2013 7.75 $15.00 $116.25
8/20/2013 7.50 $10.77 $80.78
8/20/2013 7.50 $10.77 $80.78
8/20/2013 7.50 $10.77 $80.78
8/20/2013 8.00 $15.00 $120.00
8/20/2013 5.75 $15.00 $86.25
Total Invoice: $978.51
Actual Total when you add up the numbers either in Excel or with a calculator is $978.54 and that is the amount that appears on the invoice that I need the backup to match.
I cannot figure out why it works in Excel but not in Access. I am starting to think that it is never going to be possible with Access and I hate feeling defeated. But I do understand if you need to give up. I don't want to waste your time. Thanks!!! ddm
WorkloadDate Hours Rate ExtendedTotal
8/20/2013 4.00 $10.88 $43.52
8/20/2013 4.00 $10.88 $43.52
8/20/2013 3.75 $10.88 $40.80
8/20/2013 4.00 $10.88 $43.52
8/20/2013 4.50 $10.77 $48.47
8/20/2013 4.50 $10.77 $48.47
8/20/2013 7.00 $10.77 $75.39
8/20/2013 6.50 $10.77 $70.01
8/20/2013 7.75 $15.00 $116.25
8/20/2013 7.50 $10.77 $80.78
8/20/2013 7.50 $10.77 $80.78
8/20/2013 7.50 $10.77 $80.78
8/20/2013 8.00 $15.00 $120.00
8/20/2013 5.75 $15.00 $86.25
Total Invoice: $978.51
Actual Total when you add up the numbers either in Excel or with a calculator is $978.54 and that is the amount that appears on the invoice that I need the backup to match.
I cannot figure out why it works in Excel but not in Access. I am starting to think that it is never going to be possible with Access and I hate feeling defeated. But I do understand if you need to give up. I don't want to waste your time. Thanks!!! ddm
GinaWhipp
AWF VIP
- Local time
- Today, 00:44
- Joined
- Jun 21, 2011
- Messages
- 5,876
Okay, let's try this on a copy of the database...
Put this (From http://www.consultdmw.com/access-rounding-numbers.htm)...
...in a Module and name it anything but the name of the Function. Then in your query replace Round() with dmwSymArithRoundUp().
Keeping my fingers crossed!
Put this (From http://www.consultdmw.com/access-rounding-numbers.htm)...
Code:
Function dmwSymArithRoundUp(ByVal Number As Variant, ByVal Places As Integer) _
As Double
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Note: Symmetric arithmetic rounding
' Positive and negative numbers move away from 0
' .5s rounded up
' Author: David Wallis (DMW Consultancy Limited)
' Date: 28 Aug 1997
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim dblTemp As Double
dblTemp = CDec(Nz(Number))
dblTemp = CDec(dblTemp * 10 ^ Places)
dmwSymArithRoundUp = Fix(dblTemp + 0.5 * Sgn(Number)) / 10 ^ Places
End Function...in a Module and name it anything but the name of the Function. Then in your query replace Round() with dmwSymArithRoundUp().
Keeping my fingers crossed!
- Local time
- Today, 05:44
- Joined
- Feb 19, 2013
- Messages
- 17,762
I've just created a quick table, query, form and report based on your data and it comes out with the right answer - see attached.
This suggests you have something else which is causing the problem. It may be that your report has become corrupted in some way or something else.
Suggest you rebuild your report from scratch or build up the one in the attached to look more like yours, checking as you go to see if the error reappers - if it does, at least you will know what the problem is and can take appropriate action
This suggests you have something else which is causing the problem. It may be that your report has become corrupted in some way or something else.
Suggest you rebuild your report from scratch or build up the one in the attached to look more like yours, checking as you go to see if the error reappers - if it does, at least you will know what the problem is and can take appropriate action
Oh my gosh.....
CJ... it WORKED!!!! I cannot believe it. A corrupted report???!!!! Whatever... It is fixed. Now I just need to go in and format it the way it needs to print out. You know, make it pretty. But all the functions work!!!!
I cannot believe it. 2 full days of pulling my hair out and major headaches doing all this on my own and then a day later with you guys on this forum and it is fixed!!!!
You all are terrific. Thanks for taking so much time with me and not giving up on me. I thank you from the bottom of my heart.
ddm
CJ... it WORKED!!!! I cannot believe it. A corrupted report???!!!! Whatever... It is fixed. Now I just need to go in and format it the way it needs to print out. You know, make it pretty. But all the functions work!!!!
I cannot believe it. 2 full days of pulling my hair out and major headaches doing all this on my own and then a day later with you guys on this forum and it is fixed!!!!
You all are terrific. Thanks for taking so much time with me and not giving up on me. I thank you from the bottom of my heart.
ddm
- Local time
- Yesterday, 23:44
- Joined
- Feb 28, 2001
- Messages
- 30,570
I think my next suggestion would be to make this into a scaled long integer case where each integer is stored after being FORCED to units of pennies. Then check for cases where the conversion from currency to LONG or whatever other unit you select doesn't give you what you expected.
It is my thought that if you could get this into units of pennies and then scale it with an explicit decimal point set to 2, you might avoid at least some of the headaches. Even if you have to create a temporary local table for the computation, the question would be whether you wanted exacting accuracy or extreme simplicity.
It is my thought that if you could get this into units of pennies and then scale it with an explicit decimal point set to 2, you might avoid at least some of the headaches. Even if you have to create a temporary local table for the computation, the question would be whether you wanted exacting accuracy or extreme simplicity.
Similar threads
