Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / July 2006

Tip: Looking for answers? Try searching our database.

Truncating Numbers

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
FJquestioner - 29 Jul 2006 20:18 GMT
I'm creating an accounting database.  

I'm running into trouble when I calculate  totals such as the amount I owe
on several bills.

The system stores data to several decimal places and calculates totals based
on these amounts rather than the rounded amounts (to 2 decimal places) that
are acutally displayed.  Example:

                 Displayed         Stored in System
Add this      $ 2.00              $ 1.995

Plus this      $ 2.00              $ 1.995

Totals this   $ 3.99              $ 3.99

Does anyone know how can I get the system to store all calculated monetary
fields to 2 decimal places, rounded.  Or another way to resolve this issue?

Thanks

I have all monetary fields set to the "Currency"  Data Type.

Most invoices consist of a  units * price
Gary Walter - 29 Jul 2006 23:28 GMT
> I'm creating an accounting database.
>
[quoted text clipped - 23 lines]
>
> Most invoices consist of a  units * price

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

    BUT...we wanted 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(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(Int((pSale * pTax) / 0.01 + 0.5) * 0.01)
1.3000
FJquestioner - 30 Jul 2006 01:34 GMT
Thanks a lot Gary for the quick and helpful solution.  I tried it on a couple
of formulas and it worked like a charm.

Much appreciated.

> > I'm creating an accounting database.
> >
[quoted text clipped - 94 lines]
> ?CCur(Int((pSale * pTax) / 0.01 + 0.5) * 0.01)
>  1.3000
Gary Walter - 30 Jul 2006 06:16 GMT
you're welcome -- I learned it here myself....

actually, I was afraid I might confuse
things by using an example tax rate of .02 ....

how could you trust someone's formula
that uses a rate that fails miserably to meet any
self-respecting tax authority's "gouge threshold?"

8-)

> Thanks a lot Gary for the quick and helpful solution.  I tried it on a
> couple
[quoted text clipped - 102 lines]
>> ?CCur(Int((pSale * pTax) / 0.01 + 0.5) * 0.01)
>>  1.3000
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.