Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Round numbers in reports

Posted on 2013-01-30
Medium Priority
516 Views
I have a report for lab results that I need to round numbers for.  If a sample is positive for a virus for example and 4,452,000 are found, the number on the report should be >4,000,000.  (Greater than 4 million) I also need to round down to thousands or hundreds if that is what the result is (ie: greater than one thousand) .  Is there a function that will do this or will I need to write some code to test the number length and round down accordingly?  Any number less than 100 can be left as is.
Thanks!
0
Question by:esbyrt
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 5
• 4
• 3
• +1

LVL 26

Expert Comment

ID: 38837820
Just millions, thousands or hundreds, or each power of ten?
0

LVL 25

Expert Comment

ID: 38837837
it is very easy to implement:
To round to 1000 use this:

``````Round(num / 1000) * 1000
``````

So, if you want to make a universal expression, use this:

``````Iif(num > 1000000, Round(num / 1000000) * 1000000, Iif(num > 1000, Round(num / 1000) * 1000, num))
``````
0

Author Comment

ID: 38837890
For the Iif statement, where do I put that?  The TotalNumber text field in the report (the one I want rounded) has a control source of TotalNumber in the qryCustomerResults.  Is there a place I can enter that in the report or does it need to go into the underlying query somewhere?
Thanks
0

Author Comment

ID: 38837896
jerryb30 - I need to round to 100s, 1000s, 10000s, 100000s, etc on up to say 100 million just to cover all the bases.
0

LVL 26

Expert Comment

ID: 38837908
This works as far as rounding, not as far as the text expression

Function roundNumber(num As Long) As String
Dim i As Integer
For i = 9 To 2 Step -1
If num / 10 ^ i >= 1 Then
roundNumber = ">" & (Int(num / 10 ^ i)) * 10 ^ i
Exit For
Else: roundNumber = num
End If
Next i
End Function

0

LVL 25

Expert Comment

ID: 38837919
regarding usage the function in reports, you need to do this:

In the Control Source type: =fncFormatNumber([ColumnThatNeedsToBeFormatted])

Then you can put all the code into the function. This way it is easier to maintain, and can be reused for other columns
0

LVL 52

Accepted Solution

Gustav Brock earned 2000 total points
ID: 38838755
You can use the generic function below.

Then, to obtain the rounded value:
=RoundSignificantCurrency([YourValueField],1)

or, to have a leading bracket and return a formatted string:

=IIf(RoundSignificantCurrency([YourValueField],1)<[YourValueField],">","") & Format(RoundSignificantCurrency([YourValueField],1),"Standard")

or, to leave values <100 as is:

=IIf([YourValueField]<100,[YourValueField],RoundSignificantCurrency([YourValueField],1)

``````Public Function RoundSignificantCurrency( _
ByVal curValue As Currency, _
ByVal bytSignificantDigits As Byte, _
Optional ByVal booInteger As Boolean) _
As Currency

' Rounds curValue to bytSignificantDigits digits.
'
' Performs no rounding if bytSignificantDigits is zero.
' Rounds to integer if booInteger is True.
'
' Rounds correctly curValue until max/min Value of currency type multiplied with
' 10 raised to the power of (the number of digits of the index of curValue) minus
' bytSignificantDigits.
' This equals roughly +/-922 * 10 ^ 12 for any Value of bytSignificantDigits.
'
' Requires:
'   Function Log10.
'
' 2001-10-19. Cactus Data ApS, CPH.
' 2002-04-02. Added CDec() for correcting bit errors of reals.
' 2007-04-18. Int replaced with Fix to round negative values correctly.

Dim dblTmp    As Double
Dim dblFactor As Double
Dim dblPower  As Double

' No special error handling.
On Error Resume Next

If bytSignificantDigits = 0 Or curValue = 0 Then
' Nothing to do.
Else
dblPower = Int(Log10(Abs(curValue))) + 1 - bytSignificantDigits
If booInteger = True Then
' No decimals.
If dblPower < 0 Then
dblPower = 0
End If
End If
dblFactor = 10 ^ dblPower
dblTmp = curValue / dblFactor
dblTmp = Fix(dblTmp + Sgn(dblTmp) / 2)
' Apply CDec() to correct for possible bit error when multiplying reals.
curValue = CDec(dblTmp * dblFactor)
End If

RoundSignificantCurrency = curValue

End Function

Public Function Log10( _
ByVal dblValue As Double) _
As Double

' Returns Log 10 of input dblValue.

' No error handling as this should be handled
' outside this function.
'
' Example:
'
'   If dblMyValue > 0 then
'     dblLogMyValue = Log10(dblMyValue)
'   Else
'     ' Do something else ...
'   End If

Log10 = Log(dblValue) / Log(10)

End Function
``````
/gustav
0

Author Comment

ID: 38841878
Hi folks.  I have tried both functions that have been suggested and set the control source for the text box on the report to the function plus field as shown.  Both ways I get a #Type! error in the field when the report runs.  I tried using the name of the text box in the control and the full name of the query field it's based on with the same results.  Any ideas?
Thanks for the help!
0

LVL 26

Expert Comment

ID: 38841909
What is datatype of the source field?
Does the function work when used in a query (which could be used as control source in the report)?
0

Author Comment

ID: 38842002
The TotalNumber field in the table is a long integer.  Where would I put the function in the query to test it?
Thanks!
0

LVL 26

Expert Comment

ID: 38842153
Select RoundNumber(TotalNumber) from yourTable
0

LVL 52

Expert Comment

ID: 38842912
[YourValueField] must be the fieldname from the query.
The textbox must be named differently from this.

A Long cannot cause a type failure when passed to a parameter of data type Currency.
However, it will happen if some records contain Null.

/gustav
0

Author Closing Comment

ID: 38852574
It took me a while to follow the logic through but I finally got it.  Thanks so much for the solution!
0

LVL 52

Expert Comment

ID: 38852585
Great. You are welcome!

/gustav
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are â€¦
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and installâ€¦
Whatâ€™s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macroâ€™s, and VBA code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a qâ€¦
###### Suggested Courses
Course of the Month9 days, 22 hours left to enroll