How to set a value to 0 decimal after calculation in query?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,
After a calculation in query, the value is with a lot of decimal places.

How can change the value to only 2 decimal?

Thanks a lot
 
On the menu bar, select View.Properties.
Click in the column containing the field you want to format.
Change the Format property on the property sheet as required.
Change the Decimal Places property on the property sheet as required.

Regards,
Andreas
 
In the DesignView of the Query, open the Properties window for the Field /
Column and set the Format Property of the Field as required.
 
Thank you for your advise.
Even I set the decimal places to 2. The outlooking is also 2.
When I click the value, it shows 12.3546454864546.
And after summing up the record. The total value will be different.
Please help
thank you
Daniel
 
Hi Daniel,

Maybe this will help, I don't know....

I have a program that keeps track
of tax rates for individual cities and
counties in our state, and when we
sell a product other than "in-store",
we have to apply the appropriate tax
for the city/county where that product
will be sent.

At certain intervals, we have to provide
the state with a report that sums these
values "across and down."

Even though these values (pSale*pTax) are
converted to Currency, they still contain
4 digits to the right of the decimal.

So, we might see $12.35 (formatted to
2 decimals), but the number is still 12.3546.

When we sum them "across and down" those
extra 2 digits at the end can cause the "across" sums
to not match up with "down" sums.

So, when we compute

CCur(pSale*pTax)

we want the 2 ending digits to always be "00"

There are 2 methods to round the computation.

1) CCur(Round(pSale*pTax, 2))
con:
the Round function uses Banker's Rounding
so it will round to the nearest even number
in the specific case of $x.xx50

for example,
CCur(Round(1.2850, 2)) = 1.2800

we want it to round 1.2850 up to 1.2900

2) to round to $x.xx00 and to always round $x.xx50 UP,
we use a formula such as:

CCur(Int((pSale * pTax) / 0.01 + 0.5) * 0.01)

Examples from Immediate Window:
(actually, the results in the window won't
show ending zeroes, but I have added them
to show what result would be if we had formatted
the result to "#.0000")

pSale=CCur(64.25)
pTax=0.02
?pSale*pTax
1.285
?CCur(Round(pSale*pTax, 2))
1.2800
?CCur(Round(CDec(pSale)*CDec(pTax),2))
1.2800
?CCur(Int((pSale * pTax) / 0.01 + 0.5) * 0.01)
1.2900

pSale=CCur(64.75)
pTax=0.02
?pSale*pTax
1.295
?CCur(Round(pSale*pTax, 2))
1.3000
?CCur(Round(CDec(pSale)*CDec(pTax),2))
1.3000
?CCur(Int((pSale * pTax) / 0.01 + 0.5) * 0.01)
1.3000

Actually, we use the following function:

Public Function fComputeNetTax(pSale As Currency, pTax As Single, pApplyTax)
As Currency
If pApplyTax = False Or IsNull(pSale) = True Or IsNull(pTax) = True Then
fComputeNetTax = 0
Else
fComputeNetTax = CCur(Int((CDec(pSale) * CDec(pTax)) / CDec(0.01) +
CDec(0.5)) * CDec(0.01))
End If
End Function

CDec() cures some arithmetic ills, but, since we use
Access 2000 which does directly support CDec()
in a query, we just use a user-defined function (which
has no noticeable effect on performance) where we
can use CDec().

Maybe that helps.

good luck,

gary
 
Back
Top