Solved

# I need help with significant figures in Access 2000

Posted on 2003-02-24
Medium Priority
1,103 Views
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([ReportResults]),([ReportResults]/([OrderDetails_User2]/1000)),'N/A')

I am open to all suggestions, I bow to your superior knowledge.
0
Question by:jweitzel
[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
• 20
• 19
• 9
• +1

LVL 58

Expert Comment

ID: 8011721
Paste the code below into a module, then do something like:

=Iif(IsNumeric([ReportResults]),Round(([ReportResults]/([OrderDetails_User2]/1000)),2),'N/A')

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
0

Author Comment

ID: 8021737
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
0

LVL 58

Expert Comment

ID: 8025509
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.
0

Author Comment

ID: 8029933
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

0

LVL 58

Expert Comment

ID: 8032851
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.
0

Author Comment

ID: 8038637
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
0

LVL 58

Expert Comment

ID: 8052029
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.

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.
0

Author Comment

ID: 8059062
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

0

Author Comment

ID: 8081865
I haven't lost you - have I?

Janine
0

LVL 58

Expert Comment

ID: 8088027
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.
0

Author Comment

ID: 8089673
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
0

LVL 58

Expert Comment

ID: 8123000
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(intNumSignificantDigits - lngNumOfDigits, "0")
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
0

Author Comment

ID: 8123186
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
0

LVL 58

Expert Comment

ID: 8127053
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(intNumSignificantDigits - lngNumOfDigits, "0")
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
0

Author Comment

ID: 8129693
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([ReportResults]),Round(([ReportResults]/([OrderDetails_User2]/1000)),2),'N/A')

Thanks Jim

Janine

0

LVL 58

Expert Comment

ID: 8130141
Janine,

I believe:

=RoundSignificant([ReportResults],[OrderDetails_User2])

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.
0

Author Comment

ID: 8130647
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

0

LVL 58

Expert Comment

ID: 8135917
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.
0

Author Comment

ID: 8139977
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
0

Author Comment

ID: 8175302
Hi Jim - how's it going?  Janine
0

LVL 58

Expert Comment

ID: 8180492
Jannie,

Sorry.  Got side tracked a bit.

In the query, define one column as:

PrefixSign:IIf(IsNumeric([ReportResults]),"",Left\$([ReportResults],1))

and the other as:

Result:IIf(IsNumeric([ReportResults]),RoundSignificant([ReportResults]/([OrderDetails_User2]/1000),2),'N/A')

Your resulting control then would have a control source of:

=[PrefixSign] & [Result]

Jim.
0

Author Comment

ID: 8182153
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
0

Author Comment

ID: 8182957
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
0

Author Comment

ID: 8183887
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
0

Author Comment

ID: 8184392
I don't know why that showed up twice - sorry!
Janine
0

LVL 58

Expert Comment

ID: 8184656
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.
0

Author Comment

ID: 8184944
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
0

LVL 58

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 total points
ID: 8186129
Janine,

It's pretty easy to scale things down.  Create a blank MDB then import the table, but only import it's structure (click on the advanced button at the bottom) without the data.  Also import the query and the module.

Then you can either cut & Paste a few records (with two copies of Access running) between the tables or you can attach the original table as a linked table in the new database and use an append query against the blank table.  Just specify a criteria that restricts the records (ID between certain values, go for a specific day, etc).

Doesn't take as long as you think.  Note too that you can import everything but the huge table, then do the structure on it, and copy the records.  Which way you go depends on how many other objects are in there and how big they are.

I'm not looking for a lot, just something I can use to give you an example using your own data.

Jim.
0

Author Comment

ID: 8273237
I've been pulled to another project temporarily - I will return to this one - promise!  Janine
0

LVL 58

Expert Comment

ID: 8283260
OK

Jim.
0

LVL 18

Expert Comment

ID: 8734496
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

1William
EE Cleanup Volunteer
0

Expert Comment

ID: 9619766
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....
0

LVL 58

Expert Comment

ID: 9623306
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.
0

Expert Comment

ID: 9630836
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.ControlSource = "=RoundSignificant([txtUnformatted],3)"

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
0

LVL 58

Expert Comment

ID: 9636098
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.
0

Author Comment

ID: 9636260
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
0

Expert Comment

ID: 9636380
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
0

Expert Comment

ID: 9636503
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
0

Author Comment

ID: 9636650
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
0

LVL 58

Expert Comment

ID: 9636765
<<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.
0

Expert Comment

ID: 9636833
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
0

Author Comment

ID: 9636885
What's your e-mail Chlore?  This is sort of off-topic to this forum. You can find me at jweitzel@healthscience.com.
0

Expert Comment

ID: 9636929
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
0

Expert Comment

ID: 9636979
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

0

LVL 58

Expert Comment

ID: 9642755
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.
0

Expert Comment

ID: 9644808
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
0

LVL 58

Expert Comment

ID: 9644988
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(intNumSignificantDigits - lngNumOfDigits, "0")
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

0

Expert Comment

ID: 9645768
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
0

LVL 58

Expert Comment

ID: 9646314
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.
0

## Featured Post

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
###### Suggested Courses
Course of the Month10 days, 18 hours left to enroll