Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Question Unanswered: Best way to truncate cents from currency

    I am looking for ideas on the best way to remove cents from currency without rounding. 35.49 or 35.51 should both be converted to 35 for writing to a text file.
    I am developing a function in a Access 2003 module to do this. I plan to store the value of each field in variables, looping through all records and writing out delimited lines to a file. The truncation of cents will be in the procedure only and will not affect the table.

    Jerry

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Perhaps this microsoft article can be of use to you.
    Me.Geek = True

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Nick, that article had the solution to truncation cents from currency without rounding. I used this code to verify that it works. Thank you.

    Code:
    Private Sub Test_Truncate()
    Dim value1 As Currency, value2 As Long
    
        value1 = 35.99
        value2 = Trunc_Cents(value1)
        'this displays 35
        MsgBox value2
    
    End Sub
    
    Function Trunc_Cents(var1 As Currency)
        Trunc_Cents = Int(var1 * 1) / 1
    End Function

  4. #4
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    dim mymoney
    mymoney = left(textboxname,len(tectboxname) -3)
    will work if there is always 2 places to the right of the decimal - if not you could use a mid

    but here is an example
    msgbox left("33.50",len("33.50") -3) ' place this in the immediate windows (ctrl G) and press enter
    Dale Houston, TX

  5. #5
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Lightbulb Truncation Function Needed Additional Code

    I found that using the function with negative currency did not truncate the two decimals, but rounded. Here is my revised function which converts a negative number to positive, and at the end converts the returned value to a negative number.
    Code:
    Function Trunc_Cents(var1 As Currency)
        Dim temp
        
        If var1 > 0 Then
            Trunc_Cents = Int(var1 * 1) / 1
        Else
            'different method of calculation for negative number
            var1 = var1 * -1
            temp = Int(var1 * 1) / 1
            Trunc_Cents = temp * -1
        End If
        
    End Function

  6. #6
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    msgbox left("-33.50",len("33.50") -3)

    still works with NO ROUNDING issues
    just a thought one line vs eight
    anywho that is a nice function as well
    Dale Houston, TX

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No offense, but no numeric field would hold "33.50". It would hold "33.5", as numeric data types don't store the extra digits (leading or trailing). I don't think you would want to use string formulas such as Left on a numeric field. In this instance, the Fix function may have sufficed on its own.
    Paul

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Has anyone suggested the Ceiling/Floor/Fix functions yet?
    EDIT: Yes, yes they have...
    George
    Home | Blog

  9. #9
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by pbaldy
    No offense, but no numeric field would hold "33.50". It would hold "33.5", as numeric data types don't store the extra digits (leading or trailing). I don't think you would want to use string formulas such as Left on a numeric field. In this instance, the Fix function may have sufficed on its own.
    I agree with Paul here. You could perhaps use a combination of the Left() function and the InStr() function, but that could get messy. I'd have to agree with Paul and just say use the Fix() function.
    Me.Geek = True

  10. #10
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    Thre is no perhaps - if it is a currency field the code ALWAYS works - but I do admit the fix function is a cleaner way


    In a RAD environment my snippett took 30 seconds with no review for addendums and/or errors - in a true development shop where time is not always of the essence the fix function would have been a better solution.

    Bottom line is I came in second
    Dale Houston, TX

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Dale, I don't mean to quibble, but of course it will work when you give it a specific string as in your example:

    msgbox left("-33.50",len("33.50") -3)

    If you pass it an actual currency value, which would be -33.5 in this instance, your function will return "-3", which is incorrect. In real life, the currency field could contain anything from no decimal point at all to 4 digits after the decimal. Using the Left function with a fixed length argument will simply not work. Adding various other string functions to test for the decimal point and its position could work, but represent a complicated solution to a simple problem. They are called string functions because they are meant to be used with strings. Using them for numeric values would be a last resort solution.
    Paul

  12. #12
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    To illustrate, here's your function applied to an actual currency field:

    Expr1: Left([total_money_diff],Len([total_money_diff])-3)

    As you can see, it does not produce the desired result.
    Attached Thumbnails Attached Thumbnails TestLeft.JPG  
    Paul

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by axsprog
    Thre is no perhaps - if it is a currency field the code ALWAYS works - but I do admit the fix function is a cleaner way
    Currency is a number and should not be manipulated using string functions.
    The use of Left() implicitly tells JET to convert the data to a literal string before performing operations on it.

    It may not sound like such a big problem but what happens when you do this to 100K rows? Performance hit - I think so!
    George
    Home | Blog

  14. #14
    Join Date
    Dec 2003
    Location
    Colorado
    Posts
    24

    How about this?

    cint((cint(value*100))/100)

    all : please ignore
    pb : thanks for the catch
    Last edited by xburrows; 09-12-07 at 19:11.
    Ray Burrows

  15. #15
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Doesn't meet the OP's requirements, Ray. It rounds rather than truncating.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •