Solved

How to cut off decmil places (not round)

Posted on 2010-09-24
11
1,361 Views
Last Modified: 2012-05-10
I need to take a double variable and cut off any digits two characters to the right of the decimal point.  It cannot be rounded, must be cut off.  The number of digits both sides of the decimal will vary.  I can't seem to find a method for that.  Can someone tell me a way to do this?
Thanks, Lynn
0
Comment
Question by:Lambel
  • 2
  • 2
  • 2
  • +3
11 Comments
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Int(YourNumber)

mx
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
Comment Utility
To remove all decimals, take MX's advice above.If you want to keep the first 2 decimal places:Int(YourNumber * 100) / 100
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Read that 3 times and still missed:
"two characters to the right of the decimal point."

:-(
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
:)And if perchance you meant, keep the first decimal place and truncate the rest:Int(YourNumber * 10) / 10
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
or:
Int(YourNumber *10^x)/ 10^x
where 'x' is the number of decimals you want. I've done it that way for a number of years, simply because I find it easier then counting zero's. Not as efficent of course, but I've gotten caught a couple of times by not having enough zero's on one or both sides.
FWIW,
JimD.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
JD,

I really like that method.  I'm going to add that to my toolkit as:

Public Function fnFix(SomeValue As Variant, Optional Decimals As Integer = 2) As Variant

    fnFix = Int(SomeValue * 10 ^ Decimals) / 10 ^ Decimals
   
End Function

0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
fyed,
Not quite as elegant as yours...this is from A2 days.
Also have attached RoundSignificant(), which I've never really seen before and ended up writting to answer a question here on EE a few years back.  You may find it useful, although I've never found an occasion to use it yet.    I'm not sure I really understand it's application (Patrick may have some input on that).
JimD.

' 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





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

Open in new window

0
 

Author Closing Comment

by:Lambel
Comment Utility
Thanks much!
Lynn
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
JD,

I would disagree, yours includes the necessary error handling to  prevent me from inadvertantly raising the value to the 2.3 power and handles NULLs, which is always a good idea when working with variants.


0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
I see no reason why you would pass anything else than a numeric to a rounding function, indeed as:

    Int(SomeVariant * 10 ^ Decimals) / 10 ^ Decimals

would return a Double anyway. Thus:

Public Function fnFix(dblValue As Double, Optional intDecimals As Integer = 2) As Double

    fnFix = Int(dblValue * 10 ^ intDecimals) / 10 ^ intDecimals
   
End Function


As for the posted RoundSignificant function, this is quite clumsy. Ignoring that it probably is made for some specific purpose because of the filtering for "<" and ">", it is still convoluted and - worst - will fail in any international environment where the decimal point is not a dot. Except for very special cases, numbers should _always_ be handled like numbers, not strings or anything else.

Below is a proven method which demands that you recall some of the math lessons in school about logarithmics.

Rounding like this is very useful for example for converting a pricelist neatly rounded in one currency to another currency maintaining the same level of rounding.
Here are four colums:

1: Amount in EUR
2: Amount in DKK
3: Amount in DKK rounded by 100
4: Amount in DKK rounded to two significant digits

 15            111,45        100           110
 150           1114,5        1100          1100
 1500          11145         11100         11000
 15000         111450        111500        110000
 150000        1114500       1114500       1100000

And here in the other direction:

1: Amount in DKK
2: Amount in EUR
3: Amount in EUR rounded by 100
4: Amount in EUR rounded to two significant digits

 110           14,8048452220727            0             15
 1100          148,048452220727            100           150
 11000         1480,48452220727            1500          1500
 110000        14804,8452220727            14800         15000

Have fun!

/gustav
Public Function RoundSignificantCurrency( _

  ByVal curValue As Currency, _

  ByVal bytSignificantDigits As Byte, _

  Optional ByVal booInteger As Boolean) _

  As Currency



' Rounds curValue to bytSignificantDigits digits.

'

' Performs no rounding if bytSignificantDigits is zero.

' Rounds to integer if booInteger is True.

'

' Rounds correctly curValue until max/min value of currency type multiplied with

' 10 raised to the power of (the number of digits of the index of curValue) minus

' bytSignificantDigits.

' This equals roughly +/-922 * 10 ^ 12 for any value of bytSignificantDigits.

'

' Requires:

'   Function Log10.

'

' 2001-10-19. Cactus Data ApS, CPH.

' 2002-04-02. Added CDec() for correcting bit errors of reals.

' 2007-04-18. Int replaced with Fix to round negative values correctly.

'             Parameter booInteger made Optional.



  Dim dblTmp    As Double

  Dim dblFactor As Double

  Dim dblPower  As Double

  

  ' No special error handling.

  On Error Resume Next

  

  If bytSignificantDigits = 0 Or curValue = 0 Then

    ' Nothing to do.

  Else

    dblPower = Int(Log10(Abs(curValue))) + 1 - bytSignificantDigits

    If booInteger = True Then

      ' No decimals.

      If dblPower < 0 Then

        dblPower = 0

      End If

    End If

    dblFactor = 10 ^ dblPower

    dblTmp = curValue / dblFactor

    dblTmp = Fix(dblTmp + Sgn(dblTmp) / 2)

    ' Apply CDec() to correct for possible bit error when multiplying reals.

    curValue = CDec(dblTmp * dblFactor)

  End If

  

  RoundSignificantCurrency = curValue



End Function





Public Function Log10( _

  ByVal dblValue As Double) _

  As Double



' Returns Log 10 of input dblValue.



  ' No error handling as this should be handled

  ' outside this function.

  '

  ' Example:

  '

  '   If dblMyValue > 0 then

  '     dblLogMyValue = Log10(dblMyValue)

  '   Else

  '     ' Do something else ...

  '   End If



  Log10 = Log(dblValue) / Log(10)



End Function

Open in new window

0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now