Truncating Numbers in Queries

[Mike]

My manager has asked me to resurrect a project that we gave up on a couple
years ago. We are using Access 2003 to create a proofing tool for the County
taxation software. We invested the better part of 2 years identifying
relationships, creating forms, and normalizing the data. After all that
work, the lack of an easy way to truncate numbers was a roadblock and the
database was set aside. For the tax rate calculations we are doing, we will
need to carry the rates out to 12 digits until we get to an Urban Renewal
calculation that requires us to truncate at 4 decimal places. Our I.T. folks
are willing to write something in VB but we are trying to avoid creating
complicated modules that can't be maintained by the user team. If anybody
has any ideas on how to do truncation (or even a direction to pursue), please
let me know. Any help would be great. Thanks.

 

[Wayne-I-M]

As you almost certainly dont live near me so your taxation software will not
cost me too much

: - )

Try this -
http://groups.google.com/groups/sea...uncate+group:microsoft.public.access&safe=off

 

[KARL DEWEY]

Try this --
Tax_Rate: Int([FRACTION])+Left([FRACTION]-Int([FRACTION]),6)

 

[John W. Vinson]

For the tax rate calculations we are doing, we will
need to carry the rates out to 12 digits until we get to an Urban Renewal
calculation that requires us to truncate at 4 decimal places.

Ummm...

Dead easy.

Round(<expression>, 4)

 

[Guest]

SOMETING!!

 

[Jim Bunton]

Accesss 2000 has a number of rounding functions.

eg
SELECT Dummy.DummyId, Dummy.n1, round(dummy.n1,4) AS rounded
FROM Dummy
WHERE Dummy.DummyId=1;

gives the result:
DummyId n1 rounded
1 201203.987654321 201203.9877


Jim

 

[tjhia5]

I had the same issue, and I was not interested in rounding, but
truncation.
I chose to write a function that I now use in my queries:

Function TruncDec(nbrIn As Variant, DigitsToKeep As Long) As Variant
' With more then 15 Digits to Keep will return Scientific
notification ...
' So trunctate up to the first 15 characters.

If IsNumeric(nbrIn) = False Then
TruncDec = nbrIn
Else
If InStr(1, nbrIn, ".") > 0 And InStr(1, nbrIn, ".") <
Strings.Len(nbrIn) _
And DigitsToKeep >= 0 Then
' Only process if there are numbers after the decimal

If DigitsToKeep = 0 Then
TruncDec = Fix(nbrIn)
Else
Dim Left1 As String, right1 As String ' Most be string to
keep leadins zeroes.

Left1 = Strings.Left(nbrIn, Strings.InStr(1, nbrIn, "."))
If Left1 = "." Then
Left1 = "0."
End If
right1 = Strings.Mid(nbrIn, Strings.InStr(1, nbrIn, ".") +
1, DigitsToKeep)
TruncDec = Left1 & right1
' Optionally, could return a double: TruncDec =
CDbl(Left1 & right1)
End If
Else
' If function is called with "1." just return the value sent
(it evaluates to true for IsNumeric())
TruncDec = nbrIn
End If
End If

End Function

 

[????]

I had the same issue, and I was not interested in rounding, but
truncation.
I chose to write a function that I now use in my queries:

Function TruncDec(nbrIn As Variant, DigitsToKeep As Long) As Variant
' With more then 15 Digits to Keep will return Scientific
notification ...
' So trunctate up to the first 15 characters.

If IsNumeric(nbrIn) = False Then
TruncDec = nbrIn
Else
If InStr(1, nbrIn, ".") > 0 And InStr(1, nbrIn, ".") <
Strings.Len(nbrIn) _
And DigitsToKeep >= 0 Then
' Only process if there are numbers after the decimal

If DigitsToKeep = 0 Then
TruncDec = Fix(nbrIn)
Else
Dim Left1 As String, right1 As String ' Most be string to
keep leadins zeroes.

Left1 = Strings.Left(nbrIn, Strings.InStr(1, nbrIn, "."))
If Left1 = "." Then
Left1 = "0."
End If
right1 = Strings.Mid(nbrIn, Strings.InStr(1, nbrIn, ".") +
1, DigitsToKeep)
TruncDec = Left1 & right1
' Optionally, could return a double: TruncDec =
CDbl(Left1 & right1)
End If
Else
' If function is called with "1." just return the value sent
(it evaluates to true for IsNumeric())
TruncDec = nbrIn
End If
End If

End Function