=========
QUESTION
———
Is there a Trunc or Truncate function in Microsoft Access, like there is in Microsoft Excel?

 

=========
ANSWER
———
No – but you can write your own very easily!

In Microsoft Access, create a new Module, and enter the following code:

Function Trunc(vNum As Double, vDec As Integer) As Double

Trunc = Fix(vNum * 10 ^ vDec) / 10 ^ vDec

End Function

 

Then in Access you can truncate your value by using that function as follows:

TruncatedNumber: Trunc(Number,Number of Decimal Places to truncate to)

i.e. TruncatedNumber: Trunc(10.3299999,2) would show TruncatedNumber: 10.32

 

The reason we use Fix() instead of Int() is so that even negative numbers are simply truncated and not moved “closer to 0″

 

———
You can also do it by converting your number to a string and manipulate it that way:


Option 1:
http://www.bigresource.com/Tracker/Track-ms_access-13KA18jg/

Convert the number to a string using the str() function:
str([yourNum]

Find the decimal point location:
instr([stringfield],”.”,1)

Use left and right functions to assign to your variables:
LeftOfDec = left ([stringfield], instrval – 1) …this assigns the string before the “.”
RightOfDec = right([stringfield], instrval +1) …this assigns the string after the “.”

Convert the string back to a number using the val() function

Code:

dim strpos as integer
dim NumAsString as string
dim LeftOfDec as integer
dim RightOfDec as integer

NumAsString = str(<yourNum>)
strpos = instr(strfld, “.”,1)
a = val(left(strfld, strpos -1))
b = val(right(strfld, strpos + 1))


Option 2:
http://www.bigresource.com/Tracker/Track-ms_access-13KA18jg/

Code:

dim a,b
dim tmpab()
 
tmpab=split(Cstr([Field]),”.”)
 
a=tmpab(0)
b=tmpab(1)

 

=========
EXAMPLES
———

 

=========
APPLIES TO / KEY WORDS
———
Microsoft Access
Module
VBA
Functions
Truncate
Round

 

=========
REF
———
http://www.excelforum.com/excel-programming/349291-how-about-truncating-a-decimal-as-the-trunc-function-does.html

http://windowssecrets.com/forums/showthread.php/43116-Truncate-Function-(Access-2000)

http://www.bigresource.com/Tracker/Track-ms_access-13KA18jg/

Huge thanks to our friend and colleague Steve Sharkey on this one!

 


http://www.anysitesupport.com/access-truncate-function/
http://anySiteHosting.com