# Thread: Best way to truncate cents from currency

1. Registered User
Join Date
Jan 2002
Location
Bay Area
Posts
513

## 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. Computer Monkey
Join Date
May 2005
Posts
1,191
Perhaps this microsoft article can be of use to you.

3. Registered User
Join Date
Jan 2002
Location
Bay Area
Posts
513
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. Grand Poobah
Join Date
May 2003
Location
Dallas
Posts
820
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

5. Registered User
Join Date
Jan 2002
Location
Bay Area
Posts
513

## 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. Grand Poobah
Join Date
May 2003
Location
Dallas
Posts
820
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

7. Registered User
Join Date
May 2005
Location
Posts
2,888
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.

8. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Has anyone suggested the Ceiling/Floor/Fix functions yet?
EDIT: Yes, yes they have...

9. Computer Monkey
Join Date
May 2005
Posts
1,191
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.

10. Grand Poobah
Join Date
May 2003
Location
Dallas
Posts
820
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

11. Registered User
Join Date
May 2005
Location
Posts
2,888
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.

12. Registered User
Join Date
May 2005
Location
Posts
2,888
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.

13. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
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!

14. Registered User
Join Date
Dec 2003
Location
Posts
24

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

pb : thanks for the catch
Last edited by xburrows; 09-12-07 at 19:11.

15. Registered User
Join Date
May 2005
Location