Solved

Round numbers in reports

Posted on 2013-01-30
14
460 Views
Last Modified: 2013-02-04
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
Comment
Question by:esbyrt
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 26

Expert Comment

by:jerryb30
Comment Utility
Just millions, thousands or hundreds, or each power of ten?
0
 
LVL 24

Expert Comment

by:chaau
Comment Utility
it is very easy to implement:
To round to 1000 use this:

Round(num / 1000) * 1000

Open in new window


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))

Open in new window

0
 

Author Comment

by:esbyrt
Comment Utility
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

by:esbyrt
Comment Utility
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

by:jerryb30
Comment Utility
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

select RoundNumber(somenumberField) from yourtable
0
 
LVL 24

Expert Comment

by:chaau
Comment Utility
regarding usage the function in reports, you need to do this:

In the Control Source type: =fncFormatNumber([ColumnThatNeedsToBeFormatted])
Example of Access report
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 49

Accepted Solution

by:
Gustav Brock earned 500 total points
Comment Utility
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.
'             Parameter booInteger made Optional.

  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

Open in new window

/gustav
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:esbyrt
Comment Utility
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

by:jerryb30
Comment Utility
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

by:esbyrt
Comment Utility
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

by:jerryb30
Comment Utility
Select RoundNumber(TotalNumber) from yourTable
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
[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

by:esbyrt
Comment Utility
It took me a while to follow the logic through but I finally got it.  Thanks so much for the solution!
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
Great. You are welcome!

/gustav
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now