jweitzel
asked on
I need help with significant figures in Access 2000
Hello all you excellent experts! I need help with significant figures in Access 2000.
I have a report where [ReportResults] is a known value in 2 significant number format. I need to take that number and divide it by another number [OrderDetails_User2], not in a 2 significant figure format and different each time - then divide that by 1000. What happens is that in most cases I wind up without a 2 significant figure format. For instance -1.49 / 379.60 / 1000 = 3.9251. I’d like to report only 3.9. This is not a simple decimal point issue. In some cases I may have a value of 0.0000879, I would only report 0.000087, a number such as 4,376 would be 4400 or .00764 would report as .0076, so the placement of the decimal point is not the issue. Then N/A reports when the value for [OrderDetails_User2] is 0. The other bad thing that happens is sometimes [ReportResults] has a less than symbol (<) which renders it useless as a number and therefore it won’t calculate at all. The sorry calculation I use now is:
=Iif(IsNumeric([ReportResu
I am open to all suggestions, I bow to your superior knowledge.
I have a report where [ReportResults] is a known value in 2 significant number format. I need to take that number and divide it by another number [OrderDetails_User2], not in a 2 significant figure format and different each time - then divide that by 1000. What happens is that in most cases I wind up without a 2 significant figure format. For instance -1.49 / 379.60 / 1000 = 3.9251. I’d like to report only 3.9. This is not a simple decimal point issue. In some cases I may have a value of 0.0000879, I would only report 0.000087, a number such as 4,376 would be 4400 or .00764 would report as .0076, so the placement of the decimal point is not the issue. Then N/A reports when the value for [OrderDetails_User2] is 0. The other bad thing that happens is sometimes [ReportResults] has a less than symbol (<) which renders it useless as a number and therefore it won’t calculate at all. The sorry calculation I use now is:
=Iif(IsNumeric([ReportResu
I am open to all suggestions, I bow to your superior knowledge.
ASKER
Thanks for the reply, I have ask a really stupid question though. I am a new user and to be honest have no idea how to connect this module to the formula. A quick "do this - do that" would be super, then I'll put this baby through its paces - it "looks" like just what the doctor ordered.
Thanks again
Thanks again
OK here is what you need to do. From the database container window, click on modules tab, then click the new button will popup.
Copy and paste the code I posted into that window. From the menu bar, click debug/compile (1st item). Then close it. You'll be asked for the module name. Call it something like "Numeric Functions"
Now with that code in a module, anywhere in the app you can call that function: from a query, form control, etc.
That's it!
Let me know if you have a problem.
Jim.
Copy and paste the code I posted into that window. From the menu bar, click debug/compile (1st item). Then close it. You'll be asked for the module name. Call it something like "Numeric Functions"
Now with that code in a module, anywhere in the app you can call that function: from a query, form control, etc.
That's it!
Let me know if you have a problem.
Jim.
ASKER
That part about how to make a function in modules helped a great deal thanks - you have no idea how that tiny bit of info improved my understanding....I had an Access Epiphany today!!
The code did the rounding but I am still not getting 2 significant figures and whenever the < symbol appears I still get no answer at all. I suspect the < symbol will require a remove/put back sort of code now that I am beginning to see how this thing works. I look forward to your reply!
J9
The code did the rounding but I am still not getting 2 significant figures and whenever the < symbol appears I still get no answer at all. I suspect the < symbol will require a remove/put back sort of code now that I am beginning to see how this thing works. I look forward to your reply!
J9
I'm sorry, I mis-understood your question. To make sure I understand what you mean by "significant figure", given:
1.23469406 rounds to 1.24
1.023469406 rounds to 1.024
Would I be correct?
Jim.
1.23469406 rounds to 1.24
1.023469406 rounds to 1.024
Would I be correct?
Jim.
ASKER
I work in an analytical laboratory and we report in "significnat figure" format - this is not a simple decimal point issue. For instance if I were to report a number in 2 significant figure format I would take a number such as 4,376 and report it as 4400. Or .00768 woudl report as .0077. The placement of the decimal point doesn't change. So what I need to do is take the value in ReportResults, do a calculation based on the OrderDetails_User2 field and return the results in a 2 significant figure format. To complicate the expression, ReportResults may have a "<" that would need to be stripped away and then placed on the results converted to 2 significant figure format (if appropriate). I would want the results to report N/A if the value of OrderDetails_User2 was zero. I look forward to your response - THANKS AGAIN
Janine
Janine
Janine,
Well I hunted for a bit and came up with some Excel based VBA code that I believe does exactly what you want. It will need to be modified a bit to work, but before I do that, I want to make sure it's what you need.
Please go to:
http://www.tpa.or.th/xlsiam/extreme/files/advmath/significantdigit.xls
When the spreadsheet loads, click on the 2nd tab and take a look at the table. It starts off with the numbers to be rounded, the rounding factor, the result, and the function that was used.
BTW if you don't want to execute the spreadsheet on-line, you can go here:
http://www.excelexperttraining.com/extreme/rextreme.html
and scroll down about 2/3's down the page. Look for SignificantDigits.Xls You can do a right click and download it to your system.
Let me know if this is what you need,
Jim.
Well I hunted for a bit and came up with some Excel based VBA code that I believe does exactly what you want. It will need to be modified a bit to work, but before I do that, I want to make sure it's what you need.
Please go to:
http://www.tpa.or.th/xlsiam/extreme/files/advmath/significantdigit.xls
When the spreadsheet loads, click on the 2nd tab and take a look at the table. It starts off with the numbers to be rounded, the rounding factor, the result, and the function that was used.
BTW if you don't want to execute the spreadsheet on-line, you can go here:
http://www.excelexperttraining.com/extreme/rextreme.html
and scroll down about 2/3's down the page. Look for SignificantDigits.Xls You can do a right click and download it to your system.
Let me know if this is what you need,
Jim.
ASKER
Thanks for all the research on my behalf. That excel thing looks close, but it looks like it is only applying the sigfig to the numbers after the decimal point, I would like to just report the 12.0. I found this thing which looks like it does the sigfig part right, but it lacks the "remove/replace the <" and the "report N/A if OrderDetails_User2 = 0".
Public Function significant(x, y As Integer) As String
Dim factor As Double
Dim absx As Double
Dim zz As String
Dim c As Long
Dim bPoint As Boolean
Dim sChar As String
Dim n As Long
absx = Abs(x)
If absx = 0 Then
factor = 1
Else
factor = 10 ^ Int(Log(absx) / Log(10) - y + 1)
End If
absx = Int(absx / factor + 0.5) * factor
zz = Format((IIf(x >= 0, 1, -1) * absx), "#0.00000000000000000000")
If InStr(zz, ".") > 0 Then
While Right(zz, 1) = "0"
zz = Left(zz, Len(zz) - 1)
Wend
End If
For c = 1 To Len(zz)
sChar = Mid(zz, c, 1)
If sChar > "0" And sChar <= "9" Then
n = n + 1
End If
If sChar = "." Then
bPoint = True
End If
Next
If n < y Then
If bPoint Then
zz = zz & String(y - n, "0")
End If
End If
If InStr(zz, ".") > 0 Then
If Len(zz) - InStr(zz, ".") > 3 Then
zz = Format(Val(zz), "#0.000")
End If
End If
If Right(zz, 1) = "." Then
zz = Left(zz, Len(zz) - 1)
End If
significant = zz
End Function
What do you think? Is there a way to add the other tasks? Janine
Public Function significant(x, y As Integer) As String
Dim factor As Double
Dim absx As Double
Dim zz As String
Dim c As Long
Dim bPoint As Boolean
Dim sChar As String
Dim n As Long
absx = Abs(x)
If absx = 0 Then
factor = 1
Else
factor = 10 ^ Int(Log(absx) / Log(10) - y + 1)
End If
absx = Int(absx / factor + 0.5) * factor
zz = Format((IIf(x >= 0, 1, -1) * absx), "#0.00000000000000000000")
If InStr(zz, ".") > 0 Then
While Right(zz, 1) = "0"
zz = Left(zz, Len(zz) - 1)
Wend
End If
For c = 1 To Len(zz)
sChar = Mid(zz, c, 1)
If sChar > "0" And sChar <= "9" Then
n = n + 1
End If
If sChar = "." Then
bPoint = True
End If
Next
If n < y Then
If bPoint Then
zz = zz & String(y - n, "0")
End If
End If
If InStr(zz, ".") > 0 Then
If Len(zz) - InStr(zz, ".") > 3 Then
zz = Format(Val(zz), "#0.000")
End If
End If
If Right(zz, 1) = "." Then
zz = Left(zz, Len(zz) - 1)
End If
significant = zz
End Function
What do you think? Is there a way to add the other tasks? Janine
ASKER
I haven't lost you - have I?
Janine
Janine
No, but my 2 day trip turned into 4 and I'm way behind. I haven't looked at what you posted in detail yet, but it's going to take some time to get to it. Mid next week at least.
I really am sorry. I would not have started answering this if I thought things were going to get this busy.
If you can wait, great. If not, feel free to delete the question.
Jim.
I really am sorry. I would not have started answering this if I thought things were going to get this busy.
If you can wait, great. If not, feel free to delete the question.
Jim.
ASKER
HI! I'm sorry - I didn't know you would be gone. Hope your trip was good. I am happy to wait - I look forward to your response.
Janine
Janine
Janine,
Below is the corrected (the original did not work on some values) and commented. I've also added the return of "N/A" on a zero value.
However I'm unclear what it is you need with the "<" sign.
If you could give me an example of a value that produces that result, I'll make the mods.
Jim.
Public Function RoundSignificant(varValue As Variant, intNumSignificantDigits As Integer) As String
Dim dblFactor As Double
Dim dblABSofValue As Double
Dim strFormatedValue As String
Dim lngPos As Long
Dim bPoint As Boolean
Dim sChar As String
Dim lngNumOfDigits As Long
' Check for null
varValue = Nz(varValue, 0)
' If value of zero, return "N/A"
If varValue = 0 Then
RoundSignificant = "N/A"
Else
' Get the factor
dblFactor = 10 ^ Int(Log(Abs(varValue)) / Log(10) - intNumSignificantDigits + 1)
' Based on the factor, get a new absolute value
dblABSofValue = Int(Abs(varValue) / dblFactor + 0.5) * dblFactor
' Format the value as a string.
strFormatedValue = Format((IIf(varValue >= 0, 1, -1) * dblABSofValue), "#0.00000000000000000000")
' Do we have a decimal point?
If InStr(strFormatedValue, ".") > 0 Then
' If so, chop off all zeros on the right
While right(strFormatedValue, 1) = "0"
strFormatedValue = left(strFormatedValue, Len(strFormatedValue) - 1)
Wend
End If
' Scan for the number of digits in the string
For lngPos = 1 To Len(strFormatedValue)
sChar = Mid(strFormatedValue, lngPos, 1)
If sChar > "0" And sChar <= "9" Then
lngNumOfDigits = lngNumOfDigits + 1
End If
If sChar = "." Then
bPoint = True
End If
Next
' Is the number of digits found less then the significance required?
If lngNumOfDigits < intNumSignificantDigits Then
' If so and we have decimal point, add some zeros
If bPoint = True Then
strFormatedValue = strFormatedValue & String(intNumSignificantDi
End If
End If
' Do we have anything to the right of the decimal?
' If not, remove the decimal point
If right(strFormatedValue, 1) = "." Then
strFormatedValue = left(strFormatedValue, Len(strFormatedValue) - 1)
End If
RoundSignificant = strFormatedValue
End If
End Function
Below is the corrected (the original did not work on some values) and commented. I've also added the return of "N/A" on a zero value.
However I'm unclear what it is you need with the "<" sign.
If you could give me an example of a value that produces that result, I'll make the mods.
Jim.
Public Function RoundSignificant(varValue As Variant, intNumSignificantDigits As Integer) As String
Dim dblFactor As Double
Dim dblABSofValue As Double
Dim strFormatedValue As String
Dim lngPos As Long
Dim bPoint As Boolean
Dim sChar As String
Dim lngNumOfDigits As Long
' Check for null
varValue = Nz(varValue, 0)
' If value of zero, return "N/A"
If varValue = 0 Then
RoundSignificant = "N/A"
Else
' Get the factor
dblFactor = 10 ^ Int(Log(Abs(varValue)) / Log(10) - intNumSignificantDigits + 1)
' Based on the factor, get a new absolute value
dblABSofValue = Int(Abs(varValue) / dblFactor + 0.5) * dblFactor
' Format the value as a string.
strFormatedValue = Format((IIf(varValue >= 0, 1, -1) * dblABSofValue), "#0.00000000000000000000")
' Do we have a decimal point?
If InStr(strFormatedValue, ".") > 0 Then
' If so, chop off all zeros on the right
While right(strFormatedValue, 1) = "0"
strFormatedValue = left(strFormatedValue, Len(strFormatedValue) - 1)
Wend
End If
' Scan for the number of digits in the string
For lngPos = 1 To Len(strFormatedValue)
sChar = Mid(strFormatedValue, lngPos, 1)
If sChar > "0" And sChar <= "9" Then
lngNumOfDigits = lngNumOfDigits + 1
End If
If sChar = "." Then
bPoint = True
End If
Next
' Is the number of digits found less then the significance required?
If lngNumOfDigits < intNumSignificantDigits Then
' If so and we have decimal point, add some zeros
If bPoint = True Then
strFormatedValue = strFormatedValue & String(intNumSignificantDi
End If
End If
' Do we have anything to the right of the decimal?
' If not, remove the decimal point
If right(strFormatedValue, 1) = "." Then
strFormatedValue = left(strFormatedValue, Len(strFormatedValue) - 1)
End If
RoundSignificant = strFormatedValue
End If
End Function
ASKER
You are the best! The story with the less than symbol is that in my original expression [ReportResults] is a known value that may be something like <0.05 or <1.0. Since Access sees anything with a < as a text field, it doesn't do the calculation. I can't control whether or not the less than symbol appears in my first value. So my thought was if it appears - it could be stripped off, the calculation and significant figure procedure could then occur BUT THEN the darn less than symbol would have to be put on the final result if it had appeared originally on the first value ([ReportResults]).
What do you think?
Janine
What do you think?
Janine
Jannie,
<<What do you think?>>
Well stripping off the character, then putting it back on is easy to do, but I'm unsure as to the apporach. Normally I try to keep functions as simple as possible in terms of what their used for.
For example, with the function we have, normally I make it only handle a number, then use another function to apply specific formatting (such as the N/A).
However since we have already wrapped everything into this one, I've extended it futher. Let me know if you need anything else.
Jim.
Public Function RoundSignificant(varValue As Variant, intNumSignificantDigits As Integer) As String
Dim strPrefix As String
Dim dblFactor As Double
Dim dblABSofValue As Double
Dim strFormatedValue As String
Dim lngPos As Long
Dim bolDecimalPoint As Boolean
Dim strChr As String
Dim lngNumOfDigits As Long
' Check for a prefix ('>' or '<'). If there is one,
' strip it off for now.
If left(varValue, 1) = ">" Or left(varValue, 1) < "<" Then
strPrefix = left(varValue, 1)
varValue = Val(Mid(varValue, 2))
Else
strPrefix = ""
End If
' Check for null
varValue = Nz(varValue, 0)
' If value of zero, return "N/A"
If varValue = 0 Then
RoundSignificant = "N/A"
Else
' Get the factor
dblFactor = 10 ^ Int(Log(Abs(varValue)) / Log(10) - intNumSignificantDigits + 1)
' Based on the factor, get an absolute value that's rounded.
dblABSofValue = Int(Abs(varValue) / dblFactor + 0.5) * dblFactor
' Format the value as a string.
strFormatedValue = Format((IIf(varValue >= 0, 1, -1) * dblABSofValue), "#0.00000000000000000000")
' Do we have a decimal point?
If InStr(strFormatedValue, ".") > 0 Then
' If so, chop off all zeros on the right
While right(strFormatedValue, 1) = "0"
strFormatedValue = left(strFormatedValue, Len(strFormatedValue) - 1)
Wend
End If
' Scan for the number of digits in the string
For lngPos = 1 To Len(strFormatedValue)
strChr = Mid(strFormatedValue, lngPos, 1)
If strChr > "0" And strChr <= "9" Then
lngNumOfDigits = lngNumOfDigits + 1
End If
If strChr = "." Then
bolDecimalPoint = True
End If
Next
' Is the number of digits found less then the significance required?
If lngNumOfDigits < intNumSignificantDigits Then
' If so and we have decimal point, add some zeros
If bolDecimalPoint = True Then
strFormatedValue = strFormatedValue & String(intNumSignificantDi
End If
End If
' Do we have anything to the right of the decimal?
' If not, remove the decimal point
If right(strFormatedValue, 1) = "." Then
strFormatedValue = left(strFormatedValue, Len(strFormatedValue) - 1)
End If
' Add the prefix back in if we have one
If strPrefix <> "" Then
RoundSignificant = strPrefix & strFormatedValue
Else
RoundSignificant = strFormatedValue
End If
End If
End Function
<<What do you think?>>
Well stripping off the character, then putting it back on is easy to do, but I'm unsure as to the apporach. Normally I try to keep functions as simple as possible in terms of what their used for.
For example, with the function we have, normally I make it only handle a number, then use another function to apply specific formatting (such as the N/A).
However since we have already wrapped everything into this one, I've extended it futher. Let me know if you need anything else.
Jim.
Public Function RoundSignificant(varValue As Variant, intNumSignificantDigits As Integer) As String
Dim strPrefix As String
Dim dblFactor As Double
Dim dblABSofValue As Double
Dim strFormatedValue As String
Dim lngPos As Long
Dim bolDecimalPoint As Boolean
Dim strChr As String
Dim lngNumOfDigits As Long
' Check for a prefix ('>' or '<'). If there is one,
' strip it off for now.
If left(varValue, 1) = ">" Or left(varValue, 1) < "<" Then
strPrefix = left(varValue, 1)
varValue = Val(Mid(varValue, 2))
Else
strPrefix = ""
End If
' Check for null
varValue = Nz(varValue, 0)
' If value of zero, return "N/A"
If varValue = 0 Then
RoundSignificant = "N/A"
Else
' Get the factor
dblFactor = 10 ^ Int(Log(Abs(varValue)) / Log(10) - intNumSignificantDigits + 1)
' Based on the factor, get an absolute value that's rounded.
dblABSofValue = Int(Abs(varValue) / dblFactor + 0.5) * dblFactor
' Format the value as a string.
strFormatedValue = Format((IIf(varValue >= 0, 1, -1) * dblABSofValue), "#0.00000000000000000000")
' Do we have a decimal point?
If InStr(strFormatedValue, ".") > 0 Then
' If so, chop off all zeros on the right
While right(strFormatedValue, 1) = "0"
strFormatedValue = left(strFormatedValue, Len(strFormatedValue) - 1)
Wend
End If
' Scan for the number of digits in the string
For lngPos = 1 To Len(strFormatedValue)
strChr = Mid(strFormatedValue, lngPos, 1)
If strChr > "0" And strChr <= "9" Then
lngNumOfDigits = lngNumOfDigits + 1
End If
If strChr = "." Then
bolDecimalPoint = True
End If
Next
' Is the number of digits found less then the significance required?
If lngNumOfDigits < intNumSignificantDigits Then
' If so and we have decimal point, add some zeros
If bolDecimalPoint = True Then
strFormatedValue = strFormatedValue & String(intNumSignificantDi
End If
End If
' Do we have anything to the right of the decimal?
' If not, remove the decimal point
If right(strFormatedValue, 1) = "." Then
strFormatedValue = left(strFormatedValue, Len(strFormatedValue) - 1)
End If
' Add the prefix back in if we have one
If strPrefix <> "" Then
RoundSignificant = strPrefix & strFormatedValue
Else
RoundSignificant = strFormatedValue
End If
End If
End Function
ASKER
Wow - that looks like it would work, I suspect the original expression will have to change. I tried several different combinations but my skills are very limited. I tried changing out the original "round" but that didn't work. I tried removing the N/A - no luck. How do I write the expression now?
Here is the first one you helped me with:
=Iif(IsNumeric([ReportResu
Thanks Jim
Janine
Here is the first one you helped me with:
=Iif(IsNumeric([ReportResu
Thanks Jim
Janine
Janine,
I believe:
=RoundSignificant([ReportR
is what you want assuming that [OrderDetails_User2] is the number of significant digits you want.
Basically your just dropping the IIF() check, which is now done in the function.
Jim.
I believe:
=RoundSignificant([ReportR
is what you want assuming that [OrderDetails_User2] is the number of significant digits you want.
Basically your just dropping the IIF() check, which is now done in the function.
Jim.
ASKER
I want to take ReportResults (strip the < if necessary) multiply by 1000 then divide it by OrderDetails_User 2, then apply the sigfig to that result and add the < if it occurred in ReportResults to begin with. I always want the result to be in 2 significant digits.
ReportResults = the value of analysis
OrderDetails_user2 = the air volume of the sample in liters
So if ReportResults = 0.19 and OrderDetails_user2 = 30 my result should be 6.3
If ReportResults = <0.19 than my result should be <6.3.
Whew! Janine
ReportResults = the value of analysis
OrderDetails_user2 = the air volume of the sample in liters
So if ReportResults = 0.19 and OrderDetails_user2 = 30 my result should be 6.3
If ReportResults = <0.19 than my result should be <6.3.
Whew! Janine
Janine,
I think we'll need to do this slightly different then.
First, as a general comment, your database design is incorrect.
The first rule of normalization is that you don't store two values in the same field. What your storing is an indicator of a relationship to a value (>,<, or =) and the actual numeric value in the same field. This is why were having the difficulty in handling the field.
Outside of fixing the design, you can modify the query and once again go back to using the IIF() checks. You'll need to end up with two fields:
1. The stripped character (if any)
2. The result of the calculation.
The other way to fix this is to pass the ReportResults and OrderDetails_user2 values to a function, which would do the stripping, call RoundSignificant() after doing your calculation, and return the result with the sign prefixed again.
The IIF() check is nice in that it allows your do do things within the query, be it carries a performance penalty in that both the True/False arguments are evaluated even though your only going to use one. If were talking a large number of records (>100,000), you would see a noticeable difference.
Let me know which way you'd like to handle it. The new function is the best over all; it's just a bit more work.
Jim.
I think we'll need to do this slightly different then.
First, as a general comment, your database design is incorrect.
The first rule of normalization is that you don't store two values in the same field. What your storing is an indicator of a relationship to a value (>,<, or =) and the actual numeric value in the same field. This is why were having the difficulty in handling the field.
Outside of fixing the design, you can modify the query and once again go back to using the IIF() checks. You'll need to end up with two fields:
1. The stripped character (if any)
2. The result of the calculation.
The other way to fix this is to pass the ReportResults and OrderDetails_user2 values to a function, which would do the stripping, call RoundSignificant() after doing your calculation, and return the result with the sign prefixed again.
The IIF() check is nice in that it allows your do do things within the query, be it carries a performance penalty in that both the True/False arguments are evaluated even though your only going to use one. If were talking a large number of records (>100,000), you would see a noticeable difference.
Let me know which way you'd like to handle it. The new function is the best over all; it's just a bit more work.
Jim.
ASKER
Hi Jim - Thanks for your reply. The database I'm working with is part of a larger Laboratory Information Management System= or "LIMS", I would prefer to use the IIF as I could incorporate it into other queries down the line. My record groups are very small so that would be ok.
Janine
Janine
ASKER
Hi Jim - how's it going? Janine
Jannie,
Sorry. Got side tracked a bit.
In the query, define one column as:
PrefixSign:IIf(IsNumeric([
and the other as:
Result:IIf(IsNumeric([Repo
Your resulting control then would have a control source of:
=[PrefixSign] & [Result]
Jim.
Sorry. Got side tracked a bit.
In the query, define one column as:
PrefixSign:IIf(IsNumeric([
and the other as:
Result:IIf(IsNumeric([Repo
Your resulting control then would have a control source of:
=[PrefixSign] & [Result]
Jim.
ASKER
Hi Jim! Ok - so I add two columns to my report query containing the above and then for my field control I use =[PrefixSign]&[Result]. Ok - I'm going off to give it a shot! Wish me luck!
Janine
Janine
ASKER
HI Jim,
I copied the updated RoundSigficant to the module and added the two columns and used the control source, but it isn't working - now I get two 00 in front of some results and N/A is being reported whenever a < appears rather than when OrderDetails_User2 = zero. I have a snap of the report I can forward if that will help.
Janine
I copied the updated RoundSigficant to the module and added the two columns and used the control source, but it isn't working - now I get two 00 in front of some results and N/A is being reported whenever a < appears rather than when OrderDetails_User2 = zero. I have a snap of the report I can forward if that will help.
Janine
ASKER
HI Jim,
I copied the updated RoundSigficant to the module and added the two columns and used the control source, but it isn't working - now I get two 00 in front of some results and N/A is being reported whenever a < appears rather than when OrderDetails_User2 = zero. I have a snap of the report I can forward if that will help.
Janine
I copied the updated RoundSigficant to the module and added the two columns and used the control source, but it isn't working - now I get two 00 in front of some results and N/A is being reported whenever a < appears rather than when OrderDetails_User2 = zero. I have a snap of the report I can forward if that will help.
Janine
ASKER
I don't know why that showed up twice - sorry!
Janine
Janine
Janine,
Can you send a small test MDB? I really don't have a ton of time to walk you through this, so it would be quicker this way. A table with a few records, the query, and report/form it's base on. e-mail is in my profile.
I'll be working all weekend, so send it ahead any time.
Jim.
Can you send a small test MDB? I really don't have a ton of time to walk you through this, so it would be quicker this way. A table with a few records, the query, and report/form it's base on. e-mail is in my profile.
I'll be working all weekend, so send it ahead any time.
Jim.
ASKER
Jim - this thing is part of a huge database and query. All attempts at reducing it down to a small group have failed. I'll try to recreate it in a small database but probably won't be able to send anything off til early next week.
Janine
Janine
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I've been pulled to another project temporarily - I will return to this one - promise! Janine
OK
Jim.
Jim.
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept question, points to JDettman
Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
1William
EE Cleanup Volunteer
I will leave a recommendation in the Cleanup topic area that this question is:
Accept question, points to JDettman
Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
1William
EE Cleanup Volunteer
If anyone is still looking at this post....
It has been of great help, however...
I tried both pieces of code and found a bug...
=RoundSignificant(20.43, 3)
Results in 20.40 ....
Upon further testing anytime a 0 shows up to the left of the decimal an extra 0 is added...
Chlore...
I too am building a LIMS....
It has been of great help, however...
I tried both pieces of code and found a bug...
=RoundSignificant(20.43, 3)
Results in 20.40 ....
Upon further testing anytime a 0 shows up to the left of the decimal an extra 0 is added...
Chlore...
I too am building a LIMS....
Chlore,
<<Upon further testing anytime a 0 shows up to the left of the decimal an extra 0 is added...>>
I'll fix it as long as I still have the code. Also, could you clear up exactly what you mean by the statement above?
Jim.
<<Upon further testing anytime a 0 shows up to the left of the decimal an extra 0 is added...>>
I'll fix it as long as I still have the code. Also, could you clear up exactly what you mean by the statement above?
Jim.
Jim,
Great! I was worried that this was a dead post. Ok I set up a test form with two text boxes txtSignificant and txtUnformatted where:
Me.txtSignificant.ControlS
Input: Result:
txtUnformatted txtSignificant
12.45 12.4
10.45 10.40
200.76 201.0
102.345346 102.0
111.345 111
200.000 200.00
2000.000 2000.00
2.0483 2.050
Alright, well I guess the bug is with zeros on both sides...
I hard coded the 3 in the function so each number in the txtSignificant column should have only 3 significant figures. So, the function works great until there are zeros between numbers...
Access 2002
Access 2003
Chlore
Great! I was worried that this was a dead post. Ok I set up a test form with two text boxes txtSignificant and txtUnformatted where:
Me.txtSignificant.ControlS
Input: Result:
txtUnformatted txtSignificant
12.45 12.4
10.45 10.40
200.76 201.0
102.345346 102.0
111.345 111
200.000 200.00
2000.000 2000.00
2.0483 2.050
Alright, well I guess the bug is with zeros on both sides...
I hard coded the 3 in the function so each number in the txtSignificant column should have only 3 significant figures. So, the function works great until there are zeros between numbers...
Access 2002
Access 2003
Chlore
Chlore,
Still not quite sure I understand. Check me on these:
12.45 12.4 - This is correct
10.45 10.40 - This should be 10.5
200.76 201.0 - This should be 201
102.345346 102.0 - This should be 102
111.345 111 - This is correct
200.000 200.00 - This should be 200
2000.000 2000.00 - This should be 2000
2.0483 2.050 - This should be 2.05
I'm not sure I got the correct concept of significant rounding the first time around.
Jim.
Still not quite sure I understand. Check me on these:
12.45 12.4 - This is correct
10.45 10.40 - This should be 10.5
200.76 201.0 - This should be 201
102.345346 102.0 - This should be 102
111.345 111 - This is correct
200.000 200.00 - This should be 200
2000.000 2000.00 - This should be 2000
2.0483 2.050 - This should be 2.05
I'm not sure I got the correct concept of significant rounding the first time around.
Jim.
ASKER
Hi everyone!
I still haven't been freed up to get back to this project but am thrilled to see such action! Jim, By George - I think you've got it!
Chlore! Thanks for finding that bug and I would love a chance to chat with you about your LIMS! Which software are you using for what kind of lab?
Janine
I still haven't been freed up to get back to this project but am thrilled to see such action! Jim, By George - I think you've got it!
Chlore! Thanks for finding that bug and I would love a chance to chat with you about your LIMS! Which software are you using for what kind of lab?
Janine
Jim,
yes you have it... except for 200 and 10.5
I believe that the rule in rounding (In computers mainly) occurs to the nearest even number so as not to bias recursive calculations. So for example 12.45 is rounded to 12.4 while 12.55 is rounded to 12.6 in a 3 significant figure situation...
Also, the 2000 was a bad example because has only one significant figure in the required format the only way (I know) to actually make them significant is to represent them in scientific notation. Except in my field (and Janine's as well) we end up generating reports for non sceintists who would rather see standard number format. So your function is an acceptable trade off between accuracy and readability. And to make the 200 three significant figures you would add a decimal.
So just so we are on the same page:
All of these numbers have 3 sigfigs:
0.00000200
0.000200
2.00
20.0
200.
2.00 * 10^3 'don't worry about trying to code this unless you really want a challenge
yes you have it... except for 200 and 10.5
I believe that the rule in rounding (In computers mainly) occurs to the nearest even number so as not to bias recursive calculations. So for example 12.45 is rounded to 12.4 while 12.55 is rounded to 12.6 in a 3 significant figure situation...
Also, the 2000 was a bad example because has only one significant figure in the required format the only way (I know) to actually make them significant is to represent them in scientific notation. Except in my field (and Janine's as well) we end up generating reports for non sceintists who would rather see standard number format. So your function is an acceptable trade off between accuracy and readability. And to make the 200 three significant figures you would add a decimal.
So just so we are on the same page:
All of these numbers have 3 sigfigs:
0.00000200
0.000200
2.00
20.0
200.
2.00 * 10^3 'don't worry about trying to code this unless you really want a challenge
Janine,
Wow, you posted while I was typing that... I work for a lab that tests mainly for m0ld right now, however as soon as NV_LAP comes through we will have Asbest0s. I am using Access2002 as the design software however the main system will run on Access2003. After a year I intend to port over to a Client/Server setup and maintin Access as a front end. And you?
Chlore
Wow, you posted while I was typing that... I work for a lab that tests mainly for m0ld right now, however as soon as NV_LAP comes through we will have Asbest0s. I am using Access2002 as the design software however the main system will run on Access2003. After a year I intend to port over to a Client/Server setup and maintin Access as a front end. And you?
Chlore
ASKER
Chlore -
We have a full service Industrial Hygiene lab, we do mold, asbestos, pesticides, metals, environmental stuff, etc. We've been running off a Perkin Elmer LIMS 2000 for about 15 years and are in the process of switching over to a SQL/Access based product.
Are you building a custom LIMS ? WOW!
Janine
We have a full service Industrial Hygiene lab, we do mold, asbestos, pesticides, metals, environmental stuff, etc. We've been running off a Perkin Elmer LIMS 2000 for about 15 years and are in the process of switching over to a SQL/Access based product.
Are you building a custom LIMS ? WOW!
Janine
<<I believe that the rule in rounding (In computers mainly) occurs to the nearest even number so as not to bias recursive calculations. So for example 12.45 is rounded to 12.4 while 12.55 is rounded to 12.6 in a 3 significant figure situation... >>
That's bankers rounding and usually done in situations where money is involved. Standard rounding (which is what I'm used to) is 5 and up rounds up and less rounds down. I'm assuming this is what you want.
Jim.
That's bankers rounding and usually done in situations where money is involved. Standard rounding (which is what I'm used to) is 5 and up rounds up and less rounds down. I'm assuming this is what you want.
Jim.
Janine,
Ah the competition.... *wink*
Actually we are a startup so I am sure you haven't heard of us. Yes, I started from scratch about a month ago and am currently finishing up offical reports and moving to internal c.o.c., pricing ect. (I don't think I will ever be finished). What is your company's name?
I work for S._A. I.
Chlore
Ah the competition.... *wink*
Actually we are a startup so I am sure you haven't heard of us. Yes, I started from scratch about a month ago and am currently finishing up offical reports and moving to internal c.o.c., pricing ect. (I don't think I will ever be finished). What is your company's name?
I work for S._A. I.
Chlore
ASKER
What's your e-mail Chlore? This is sort of off-topic to this forum. You can find me at jweitzel@healthscience.com
Jim,
Yes standard rounding is fine, however I think your function (and all floating point calculations preformed on a computer for that matter) use "banker's rounding". Here are some of the results for 3 significant figures:
12.05 12.1
12.15 12.2
12.25 12.3
12.35 12.3
12.45 12.4
12.55 12.6
12.65
Yes standard rounding is fine, however I think your function (and all floating point calculations preformed on a computer for that matter) use "banker's rounding". Here are some of the results for 3 significant figures:
12.05 12.1
12.15 12.2
12.25 12.3
12.35 12.3
12.45 12.4
12.55 12.6
12.65
Jim,
*stupid tab enter*
sorry bout making this a dual post
To continue:
12.65 12.7
12.75 12.8
12.85 12.9
12.95 12.9
So as you see there is no rounding to 12.5 and two 12.3's and 12.9's
I understand standard rounding better, however I don't have a problem yielding to a statistically sounder method.
Chlore
*stupid tab enter*
sorry bout making this a dual post
To continue:
12.65 12.7
12.75 12.8
12.85 12.9
12.95 12.9
So as you see there is no rounding to 12.5 and two 12.3's and 12.9's
I understand standard rounding better, however I don't have a problem yielding to a statistically sounder method.
Chlore
Chlore,
Well now I'm really confused. Banker's rounding rounds up on even numbers and down on odd. Standard rounding is 1 to 4 rounds down, and 5-9 rounds up. What you've posted as an example matches neither.
This is what I believe you want based on a value of 12.345:
S#'s Result
6 12.3450
5 12.345
4 12.35
3 12.4
2 12
1 10
based on a value of 2000.76543:
S#'s Result
10 2000.765430
9 2000.76543
8 2000.765
7 2000.77
6 2000.78
5 2000.8
4 2001
3 2000
2 2000
1 2000
Comments? or can you point me to a reference that explains what you want?
Jim.
Well now I'm really confused. Banker's rounding rounds up on even numbers and down on odd. Standard rounding is 1 to 4 rounds down, and 5-9 rounds up. What you've posted as an example matches neither.
This is what I believe you want based on a value of 12.345:
S#'s Result
6 12.3450
5 12.345
4 12.35
3 12.4
2 12
1 10
based on a value of 2000.76543:
S#'s Result
10 2000.765430
9 2000.76543
8 2000.765
7 2000.77
6 2000.78
5 2000.8
4 2001
3 2000
2 2000
1 2000
Comments? or can you point me to a reference that explains what you want?
Jim.
Jim,
Sorry about the confusion what I really want is standard rounding for report generation ( A banker's rounding function may be useful if I ever have to do recursive calculations, however I usually do all calculations with doubles and then compute significant figures by hand and then apply formating.) I just assumed your function was based on bakner's rounding because of the wierd results: what I posted were direct values from your function, where the left column were inputs and the right were the result of 3 significant figure rounding. (I agree I am not sure what is happening with the results I previously posted.)
You are correct, however I hate to be nitpicky, but then again getting it right is the whole point:
based on: 2000.76543
"8 2000.765" should be: 8 2000.7654
"7 2000.77" should be: 7 2000.765
"6 2000.78" should be: 6 2000.77
Chlore
Sorry about the confusion what I really want is standard rounding for report generation ( A banker's rounding function may be useful if I ever have to do recursive calculations, however I usually do all calculations with doubles and then compute significant figures by hand and then apply formating.) I just assumed your function was based on bakner's rounding because of the wierd results: what I posted were direct values from your function, where the left column were inputs and the right were the result of 3 significant figure rounding. (I agree I am not sure what is happening with the results I previously posted.)
You are correct, however I hate to be nitpicky, but then again getting it right is the whole point:
based on: 2000.76543
"8 2000.765" should be: 8 2000.7654
"7 2000.77" should be: 7 2000.765
"6 2000.78" should be: 6 2000.77
Chlore
Chlore,
Sorry about that. I just messed up in posting. OK so we have:
based on a value of 2000.76543:
S#'s Result
10 2000.765430
9 2000.76543
8 2000.7654
7 2000.765
6 2000.77
5 2000.8
4 2001
3 2000
2 2000
1 2000
Below is the corrected function. Let me know if you need anything else.
Jim.
Public Function RoundSignificant(varValue As Variant, intNumSignificantDigits As Integer) As String
Dim strPrefix As String
Dim dblFactor As Double
Dim dblABSofValue As Double
Dim strFormatedValue As String
Dim lngPos As Long
Dim bolDecimalPoint As Boolean
Dim strChr As String
Dim lngNumOfDigits As Long
' Check for a prefix ('>' or '<'). If there is one,
' strip it off for now.
If Left(varValue, 1) = ">" Or Left(varValue, 1) < "<" Then
strPrefix = Left(varValue, 1)
varValue = Val(Mid(varValue, 2))
Else
strPrefix = ""
End If
' Check for null
varValue = Nz(varValue, 0)
' If value of zero, return "N/A"
If varValue = 0 Then
RoundSignificant = "N/A"
Else
' Get the factor
dblFactor = 10 ^ Int(Log(Abs(varValue)) / Log(10) - intNumSignificantDigits + 1)
' Based on the factor, get an absolute value that's rounded.
If dblFactor = 1 Then
dblABSofValue = CLng((Abs(varValue) / dblFactor))
ElseIf dblFactor > 1 Then
dblABSofValue = CLng((Abs(varValue) / dblFactor) + (0.5 / dblFactor))
Else
dblABSofValue = CLng((Abs(varValue) / dblFactor) + (0.5 * dblFactor))
End If
dblABSofValue = dblABSofValue * dblFactor
' Format the value as a string.
strFormatedValue = Format((IIf(varValue >= 0, 1, -1) * dblABSofValue), "#0.00000000000000000000")
' Do we have a decimal point?
If InStr(strFormatedValue, ".") > 0 Then
' If so, chop off all zeros on the right
While Right(strFormatedValue, 1) = "0"
strFormatedValue = Left(strFormatedValue, Len(strFormatedValue) - 1)
Wend
End If
' Scan for the number of digits in the string
For lngPos = 1 To Len(strFormatedValue)
strChr = Mid(strFormatedValue, lngPos, 1)
If strChr >= "0" And strChr <= "9" Then
lngNumOfDigits = lngNumOfDigits + 1
End If
If strChr = "." Then
bolDecimalPoint = True
End If
Next
' Is the number of digits found less then the significance required?
If lngNumOfDigits < intNumSignificantDigits Then
' If so and we have decimal point, add some zeros
If bolDecimalPoint = True Then
strFormatedValue = strFormatedValue & String(intNumSignificantDi
End If
End If
' Do we have anything to the right of the decimal?
' If not, remove the decimal point
If Right(strFormatedValue, 1) = "." Then
strFormatedValue = Left(strFormatedValue, Len(strFormatedValue) - 1)
End If
' Add the prefix back in if we have one
If strPrefix <> "" Then
RoundSignificant = strPrefix & strFormatedValue
Else
RoundSignificant = strFormatedValue
End If
End If
End Function
Sorry about that. I just messed up in posting. OK so we have:
based on a value of 2000.76543:
S#'s Result
10 2000.765430
9 2000.76543
8 2000.7654
7 2000.765
6 2000.77
5 2000.8
4 2001
3 2000
2 2000
1 2000
Below is the corrected function. Let me know if you need anything else.
Jim.
Public Function RoundSignificant(varValue As Variant, intNumSignificantDigits As Integer) As String
Dim strPrefix As String
Dim dblFactor As Double
Dim dblABSofValue As Double
Dim strFormatedValue As String
Dim lngPos As Long
Dim bolDecimalPoint As Boolean
Dim strChr As String
Dim lngNumOfDigits As Long
' Check for a prefix ('>' or '<'). If there is one,
' strip it off for now.
If Left(varValue, 1) = ">" Or Left(varValue, 1) < "<" Then
strPrefix = Left(varValue, 1)
varValue = Val(Mid(varValue, 2))
Else
strPrefix = ""
End If
' Check for null
varValue = Nz(varValue, 0)
' If value of zero, return "N/A"
If varValue = 0 Then
RoundSignificant = "N/A"
Else
' Get the factor
dblFactor = 10 ^ Int(Log(Abs(varValue)) / Log(10) - intNumSignificantDigits + 1)
' Based on the factor, get an absolute value that's rounded.
If dblFactor = 1 Then
dblABSofValue = CLng((Abs(varValue) / dblFactor))
ElseIf dblFactor > 1 Then
dblABSofValue = CLng((Abs(varValue) / dblFactor) + (0.5 / dblFactor))
Else
dblABSofValue = CLng((Abs(varValue) / dblFactor) + (0.5 * dblFactor))
End If
dblABSofValue = dblABSofValue * dblFactor
' Format the value as a string.
strFormatedValue = Format((IIf(varValue >= 0, 1, -1) * dblABSofValue), "#0.00000000000000000000")
' Do we have a decimal point?
If InStr(strFormatedValue, ".") > 0 Then
' If so, chop off all zeros on the right
While Right(strFormatedValue, 1) = "0"
strFormatedValue = Left(strFormatedValue, Len(strFormatedValue) - 1)
Wend
End If
' Scan for the number of digits in the string
For lngPos = 1 To Len(strFormatedValue)
strChr = Mid(strFormatedValue, lngPos, 1)
If strChr >= "0" And strChr <= "9" Then
lngNumOfDigits = lngNumOfDigits + 1
End If
If strChr = "." Then
bolDecimalPoint = True
End If
Next
' Is the number of digits found less then the significance required?
If lngNumOfDigits < intNumSignificantDigits Then
' If so and we have decimal point, add some zeros
If bolDecimalPoint = True Then
strFormatedValue = strFormatedValue & String(intNumSignificantDi
End If
End If
' Do we have anything to the right of the decimal?
' If not, remove the decimal point
If Right(strFormatedValue, 1) = "." Then
strFormatedValue = Left(strFormatedValue, Len(strFormatedValue) - 1)
End If
' Add the prefix back in if we have one
If strPrefix <> "" Then
RoundSignificant = strPrefix & strFormatedValue
Else
RoundSignificant = strFormatedValue
End If
End If
End Function
Jim,
That works great! Thanks for the updated code. The only change I made is to leave the decimal when the number of sigfigs equals the length of the string so a 3 sigfig "200" would appear as "200."
Origional:
' Do we have anything to the right of the decimal?
' If not, remove the decimal point
If Right(strFormatedValue, 1) = "." Then
strFormatedValue = Left(strFormatedValue, Len(strFormatedValue) - 1)
End If
Modified:
' Do we have anything to the right of the decimal And are the number of sigfigs less than the string length -1?
' If not, remove the decimal point
If Right(strFormatedValue, 1) = "." And (Len(strFormatedValue) - 1) > intNumSignificantDigits Then
strFormatedValue = Left(strFormatedValue, Len(strFormatedValue) - 1)
End If
That pretty much is it thanks. I am still trying to figure out that factoring thing nice job.
Chlore
That works great! Thanks for the updated code. The only change I made is to leave the decimal when the number of sigfigs equals the length of the string so a 3 sigfig "200" would appear as "200."
Origional:
' Do we have anything to the right of the decimal?
' If not, remove the decimal point
If Right(strFormatedValue, 1) = "." Then
strFormatedValue = Left(strFormatedValue, Len(strFormatedValue) - 1)
End If
Modified:
' Do we have anything to the right of the decimal And are the number of sigfigs less than the string length -1?
' If not, remove the decimal point
If Right(strFormatedValue, 1) = "." And (Len(strFormatedValue) - 1) > intNumSignificantDigits Then
strFormatedValue = Left(strFormatedValue, Len(strFormatedValue) - 1)
End If
That pretty much is it thanks. I am still trying to figure out that factoring thing nice job.
Chlore
Chlore,
<<I am still trying to figure out that factoring thing nice job.>>
If I had more time, I'd probably could make that a bit more efficent. I started breaking out the calculation into small steps when I had problems. Fix() and Int() were not working as I expected them to be and I'm still not sure why they did not. If they had, the actual calculation would have been a whole lot simpler:
1. Figure the factor
2. Multiply by the factor
3. Take the Fix() of that
4. Divide by the factor.
Which could have been done in two statements. In any case, the function as you've found does work, and since I'm short on time, I won't play with it further.
Jim.
<<I am still trying to figure out that factoring thing nice job.>>
If I had more time, I'd probably could make that a bit more efficent. I started breaking out the calculation into small steps when I had problems. Fix() and Int() were not working as I expected them to be and I'm still not sure why they did not. If they had, the actual calculation would have been a whole lot simpler:
1. Figure the factor
2. Multiply by the factor
3. Take the Fix() of that
4. Divide by the factor.
Which could have been done in two statements. In any case, the function as you've found does work, and since I'm short on time, I won't play with it further.
Jim.
=Iif(IsNumeric([ReportResu
Note that the functions handles Null's by returning 0. May not be what you want.
Jim.
' Accepts: a variant value
' Purpose: converts multiplace decimal numbers
' Returns: a number rounded to d decimal places
' or a zero if the value it was called for was null
' If d is negative or null d is set to 0 and the function is like Int()
' In any case d is set to Int(d)!
' Author: Marcus O. M. Grabe, CIS 100120,1405
' Please send a message, if you like it or if you have any suggestions.
Function Round(n, d)
On Error Resume Next
If IsNull(n) Or IsNull(d) Then
Round = 0
Else
If d < 0 Then
d = 0
Else
d = Int(d)
End If
Round = CLng(n * (10 ^ d)) / (10 ^ d)
End If
End Function