Round numbers in reports
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!
Thanks!
jerryb30
Just millions, thousands or hundreds, or each power of ten?
it is very easy to implement:
To round to 1000 use this:
So, if you want to make a universal expression, use this:
To round to 1000 use this:
Round(num / 1000) * 1000So, 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))ASKER
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
Thanks
ASKER
jerryb30 - I need to round to 100s, 1000s, 10000s, 100000s, etc on up to say 100 million just to cover all the bases.
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(somenumberFiel
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(somenumberFiel
regarding usage the function in reports, you need to do this:
In the Control Source type: =fncFormatNumber([ColumnTh
Then you can put all the code into the function. This way it is easier to maintain, and can be reused for other columns
In the Control Source type: =fncFormatNumber([ColumnTh
Then you can put all the code into the function. This way it is easier to maintain, and can be reused for other columns
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
Thanks for the help!
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)?
Does the function work when used in a query (which could be used as control source in the report)?
ASKER
The TotalNumber field in the table is a long integer. Where would I put the function in the query to test it?
Thanks!
Thanks!
Select RoundNumber(TotalNumber) from yourTable
[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
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
ASKER
It took me a while to follow the logic through but I finally got it. Thanks so much for the solution!
Great. You are welcome!
/gustav
/gustav