Solved Report fields not calculating correctly off of rounded totals (1 Viewer)

[donkey9972]

Hi all, first let me apologize as I am not the best or even real good at naming convention, making anything look nice. I am just trying to get the job done.

Now the problem I am having is on the rptCasesCount1 report.
Once you open it you will see 6 blocks in red and blue.

The first one with the total 2122.82 should be rounded as I did in the block below it that is showing 2123.
Same thing for the next 2 blocks where one shows -1507.82 and the one below is shows -1508.

Now here is where the problem actually arises.

The final 2 blocks to the far right shows {$733.98} and in the block under it 734.06.
The block with 734.06 is simply a text box.
The block above it is showing what the calculation comes out to using the values in the top blocks in blue all the way across.
I need them to calculate off the bottom blue blocks and be able to come up with the actual total of {$734.06}
I have used the fields in the bottom blocks with the rounded figures but when it comes to the final block it always calculates off the non-rounded numbers.
I do not know how to fix this.
Please help.

 

[June7]

This rounding is done with textbox properties. This is a display setting, does not change value that is used in other calcs. If you want a rounded number then need to use a Function. Keep in mind Round() function in Access uses banker's (aka even/odd) rounding. E.g., 4.245 rounds to 4.24 (not 4.25) and 4.235 rounds to 4.24 as expected. However, Format() does not but the result is a string, not a number. I have a custom VBA function to get around the even/odd rule - if that is critical for you, consider:

Function RRound(FieldValue As Variant, Optional intPos As Integer = 0) As Variant
'--------------------------------------------------
' Function RRound() rounds value to designated decimal position.
' If argument does not contain data, RRound returns null.
' Use because intrinsic Round uses even/odd (banker's) rounding.
' Also, Format and FormatNumber functions don't use even/odd but
' generate a string result which is often inconvenient.
'--------------------------------------------------
Dim strZeros As String
Dim i As Integer
If intPos = 0 Then
    strZeros = 0
Else
    For i = 1 To intPos
        strZeros = strZeros & 0
    Next
    strZeros = "0." & strZeros
End If
RRound = IIf(Not IsNull(FieldValue), Val(Format(FieldValue, strZeros)), Null)
End Function

Why did you convert every value with CDbl() function?

 

[donkey9972]

I converted them that way because it was the only way I knew how to accomplish what I was doing.

 

[June7]

But why? What happens if you don't use CDbl() function? Especially don't see need for with addition and subtraction calcs. Using Nz() I do understand.

 

[mike60smart]

Why are all the fields for your process recorded in 1 table?

 

[donkey9972]

Hi, I am sorry for my reply being so delayed. I was sick for about almost a week, then I was finally back at work and tried to implement the code you provided to me June7. I do appreciate the code, however I am not proficient at all with VBA and I get myself in over my head on this quite often. That all being said, I did not know anything about Nz(), I was having a problem where I could not get the fields to add or subtract correctly and I came across the CDbl() and it worked for what I was needing, so I went with it. I have had no issues with it up to this point, could that be part of the problem why my fields are not working how I was wanting them to with my current problem?

Also I think I am not doing something correctly using this code. I put it in a module as a public function, then I tried to call it on the report using the on load, but all I get is an error. I am not sure what I am doing wrong.

Also mike60smart, this is just a small portion of my database. I removed the majority of it just for this purpose. All of this is needed for 4 forms and 2 reports. I did not see the need to use a relationship on this part since all of the data that would be entered was duplicates anyway, and stacking it all into one table made it easier without people entering identical information in multiple areas.

Also I am not running any Windows OS at home, I am using Linux, so any help I do get has to wait to be tried when I am at work and have a chance to try it. I would reply from work with my results either failing on my part or success on yours, but my organization has blocked all forum site usage along with a whole slew of other helpful sites.

 

[June7]

Arithmetic with Null returns Null. Nz() assures that a value is provided so that a result can be returned even if any input in expression is Null. It is a VBA function - review http://allenbrowne.com/QueryPerfIssue.html#Nz

Exactly how did you implement the RRound() function? Did you place it in a general module so that it is available to queries and other code modules and textboxes? How did you reference it in an expression?

Your data structure is not normalized (it's basically a spreadsheet) and will likely be a regular source of frustration. Query will probably perform poorly with large dataset.

I used Notepad to remove all the CDbl from query. Doesn't seem to impact calcs. Query might be a tiny bit faster.

You have a general module procedure that uses Me reference. Me is only valid behind forms and reports. Your code will not compile with this error.

 

[donkey9972]

I created a new module, pasted the round code. Then on report load I used call rround.

 

[June7]

"on report load I used call rround" - exactly how? Why would you call in report load? Provide code or database.

 

[donkey9972]

I do not fully understand, that was the code I used, On the field report load I used the code Call RRound

I tried to put Call RRound() but the parenthesis kept disappearing.

 

[June7]

Can't just "Call" a function, certainly not a function that returns a value. Purpose of this function is to round a number to some given decimal position and return that result for further use.

In query:

SELECT RRound(somefieldname, 2) AS RoundedField FROM tablename;

In textbox:

=RRound(somefieldname, 2)

In VBA:

Dim dblN As Double
dblN = RRound(somefieldname, 2)

Can pass a more complex expression to the function:

RRound(Nz(field1,0) * Nz(field2,0), 2)

 

[donkey9972]

Alright, I will give this a shot once I am back at work tonight. If I am unable to due to time constraints I will try it tomorrow night. Thank you and my fingers are crossed.

 

[June7]

This function is only suggested for use if the even/odd rounding rule is an issue for your calcs. Otherwise, just use the intrinsic Round() function.

 

[donkey9972]

June7 I have a question regarding something you posted.
"In query:
SELECT RRound(somefieldname, 2) AS RoundedField FROM tablename;"

The fields I am having trouble with are only fields on the report. All their calculations are done on the report. How do I implement something like what I quoted?

 

[gemma-the-husky]

I haven't looked at the detail, but if you sum transactions, you need to understand whether your process is rounding each transaction first and then summing, or summing, and then rounding the total. (or even rounding elements used in the calculation of each transaction, so that the transaction value used in the sum is not even the same). That may be the issue.

 

[June7]

I showed example of calling function from textbox. I also showed it using an expression. As gemma said, determine if you should round each value or round the calculated result.

Can reference fields or textboxes. Use it in the same manner as the intrinsic Round() or CDbl() functions. I still don't know why you use CDbl.

Example using in your report: RRound(Nz([Text246],0)*0.07,0)

 

[donkey9972]

Ok June7, it took me quite awhile, probably because I am slow in the head, but after rereading your posts, about 50 times. Then working and reworking and reworking numerous times, I was finally able to get it to round correctly. Thank you.