rounding problem

G

Guest

Hello,

Is there any way of using the access round function with negative numbers?
In excel I use round to account for significant figures {eg round(4563,-1)
will return 4560}. I need to do this in access and I get an #error. Any
suggestions greatly apprecated.

Thanks

Susan
 
J

James A. Fortune

Susan said:
Hello,

Is there any way of using the access round function with negative numbers?
In excel I use round to account for significant figures {eg round(4563,-1)
will return 4560}. I need to do this in access and I get an #error. Any
suggestions greatly apprecated.

Thanks

Susan

It's possible to run Excel functions in Access by setting a reference to
the Excel Object Library. You would use something like:

Excel.WorksheetFunction.Round

That said, I posted some code that starts to address significant figures
in Access here:

http://groups.google.com/group/microsoft.public.access/msg/6f122dd470e8a6a0

Besides the '+ 0.0000001' that I have to deal with eventually, I have an
uneasy feeling that the code I posted is likely to have at least one
remaining problem. Part of the reason for separate functions for
calculation and display is the "helpfulness" of Access in dropping
trailing 0's from numbers. If you try the code and get back any
incorrect results from your tests, please let me know what inputs cause
problems.

Thanks,

James A. Fortune
(e-mail address removed)
 
G

Guest

Thanks for that. Works beautifully.

Much more graceful then the solution that I found;
IIf([C14mg_kg]<10,Round([C14mg_kg],2),IIf([C14mg_kg]<100,Round([C14mg_kg],1),IIf([C14mg_kg]<1000,Round([C14mg_kg],0),IIf([C14mg_kg]<10000,Format(Format([C14mg_kg]/10,"#")*10,"#"),Format(Format([C14mg_kg]/100,"#")*100,"#")))))

The only problem that I noticed, and I'm happy to live with, is that it
doesn't display an ending 0 after the decimal point even if it is
significant. Note the second value...0.760 is showing as 0.76

eg:

Result Raw value
0.401 0.400612291798398
0.76 0.760453060392554
0.746 0.745914822100463
11.5 11.5388807867355
2.97 2.96624702231286
5.66 5.65892504778469
5.94 5.93820045585862

Thanks again!

Susan
 
J

James A. Fortune

Susan said:
Thanks for that. Works beautifully.

Much more graceful then the solution that I found;
IIf([C14mg_kg]<10,Round([C14mg_kg],2),IIf([C14mg_kg]<100,Round([C14mg_kg],1),IIf([C14mg_kg]<1000,Round([C14mg_kg],0),IIf([C14mg_kg]<10000,Format(Format([C14mg_kg]/10,"#")*10,"#"),Format(Format([C14mg_kg]/100,"#")*100,"#")))))

The only problem that I noticed, and I'm happy to live with, is that it
doesn't display an ending 0 after the decimal point even if it is
significant. Note the second value...0.760 is showing as 0.76

eg:

Result Raw value
0.401 0.400612291798398
0.76 0.760453060392554
0.746 0.745914822100463
11.5 11.5388807867355
2.97 2.96624702231286
5.66 5.65892504778469
5.94 5.93820045585862

Thanks again!

Susan

Susan,

I'm glad the code is helping you. Thanks for finding that problem.
I'll take a look at the FormatSF function within the next few days. So
far, SetSF looks fine.

James A. Fortune
(e-mail address removed)
 
J

James A. Fortune

Susan said:
Thanks for that. Works beautifully.

Much more graceful then the solution that I found;
IIf([C14mg_kg]<10,Round([C14mg_kg],2),IIf([C14mg_kg]<100,Round([C14mg_kg],1),IIf([C14mg_kg]<1000,Round([C14mg_kg],0),IIf([C14mg_kg]<10000,Format(Format([C14mg_kg]/10,"#")*10,"#"),Format(Format([C14mg_kg]/100,"#")*100,"#")))))

The only problem that I noticed, and I'm happy to live with, is that it
doesn't display an ending 0 after the decimal point even if it is
significant. Note the second value...0.760 is showing as 0.76

eg:

Result Raw value
0.401 0.400612291798398
0.76 0.760453060392554
0.746 0.745914822100463
11.5 11.5388807867355
2.97 2.96624702231286
5.66 5.65892504778469
5.94 5.93820045585862

Thanks again!

Susan

Susan,

I'm still working on this problem. In addition to the problem you
found, I'm working on some other aspects of the general problem to
understand why having the SetSF calulation in hand saves computational
effort as opposed to using a more intuitive, yet longer method to get
the FormatSF result.

James A. Fortune
(e-mail address removed)
 
B

Blairn

I don't know whether you have solved this question but I use the
following and I haven't broken it yet:

Y: Round(X/10^(Int(Log(Abs(X)/Log(10))-(W-1)))*10^(Int(Log(Abs(X))/
Log(10))-(W-1))

Where:
W is the number of desired significant figures
X is the original number
Y is the number, X rounded to W significant figures

Blair Nimmo
 
J

James A. Fortune

Blairn said:
I don't know whether you have solved this question but I use the
following and I haven't broken it yet:

Y: Round(X/10^(Int(Log(Abs(X)/Log(10))-(W-1)))*10^(Int(Log(Abs(X))/
Log(10))-(W-1))

Where:
W is the number of desired significant figures
X is the original number
Y is the number, X rounded to W significant figures

Blair Nimmo

Blairn,

Thanks for the input. Your expression looks pretty close to what I have
in SetSF, which seems to be working perfectly. The problem I am working
on is:

Given an expression that represents the numeric value to a given number
of significant figures, create a string that works in all cases that
formats that number to a number of significant figures that may be
different than the original number of significant figures. To examine
the problem in detail, I am creating a long, intuitive version (I'm not
quite done with that either) so that I can see what the issues are and
that I can understand why the output of SetSF allows for the amount of
optimization of the code that is does:

Public Function FormatSF2(ByVal dblX As Double, intPlaces As Integer) As
String
Dim intExponent As Integer
Dim intSign As Integer
Dim strDigits As String
Dim strChar As String
Dim strTemp As String
Dim I As Integer

If dblX <> 0 Then
If dblX < 0 Then
'work with positive values and put the sign in at the end
intSign = -1
dblX = -dblX
Else
intSign = 1
End If
strDigits = "111111111111111111111"
Do Until intPlaces >= Len(strDigits)
strDigits = ""
For I = 1 To Len(CStr(dblX))
strChar = Mid(CStr(dblX), I, 1)
If IsNumeric(strChar) Then strDigits = strDigits & strChar
Next I
'Get rid of any leading 0's
Do While Left(strDigits, 1) = "0"
strDigits = Right(strDigits, Len(strDigits) - 1)
Loop
If intPlaces >= Len(strDigits) Then
strTemp = strDigits & String(intPlaces - Len(strDigits), "0")
Else
'FormatSF needs to chop the number even more to make it fit
'Just chopping isn't correct
dblX = SetSF(dblX, intPlaces)
End If
Loop
'Put in the decimal point, if applicable
intExponent = Int(Log(dblX) / Log(10#) + 0.0000000001)
If intExponent > 0 Then
If Len(strDigits) > intExponent + 1 Then
strTemp = Left(strTemp, intExponent + 1) & "." & Right(strTemp,
Len(strTemp) - intExponent - 1)
Else
strTemp = Left(strTemp, intExponent + 1)
End If
ElseIf intExponent < 0 Then
strTemp = "0." & String(CLng(-intExponent), "0") & strTemp
Else
'The number is between 1 and 10
strTemp = Left(strDigits, 1) & "." & Right(strTemp, Len(strTemp) - 1)
End If
If intSign = -1 Then strTemp = "-" & strTemp
Else
strTemp = "0"
If intPlaces > 1 Then
strTemp = strTemp & "." & String(intPlaces - 1, "0")
End If
End If
FormatSF2 = strTemp
End Function

Obviously I have not tried to optimize this at all since it is merely
for didactic purposes. I suspect that the final change(s) to the
original FormatSF function to fix the problem will be minimal,
especially if the number of significant digits does not change between
the calculation and presentation.

James A. Fortune
(e-mail address removed)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Rounding Trick 1
Rounding 10
Found a Bug or Error in Access 2002-2003 Round Function 3
Bankers Rounding 1
Round Function 3
Excel Stop Excel from displaying rounded values 4
Stop Rounding in Forms 2
0 Decimal places/round function. 4

Top