rounding up numbers in Access database

D

drumette

I am setting up an invoice where a % discount must be displayed and figured
into the cost of every item entered. The database shows a rounded up figure
but takes the pre-rounded figure to do calculations. Example: $15.30 - 5% =
$14.54 but the database takes $14.535 to calculate the grand total resulting
in an incorrect total amount. I have tried everypossible way to fix it.
Any help is appreciated. Thank you.
 
M

M.L. Sco Scofield

Van,

Were you aware that the intrinsic VBA Round function does "bankers" rounding
which means that 3.5 *and* 4.5 are *both* 4?

For an explanation, see
http://support.microsoft.com/default.aspx?scid=kb;en-us;225330.

For a VBA round function that does what most of us think of as "normal"
rounding, see
http://support.microsoft.com/default.aspx?scid=kb;en-us;196652. It has
descriptions and functions for more kinds of rounding than any of up would
ever care to know! :)

--

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Useful Metric Conversion #17 of 19: 1 billion billion picolos = 1 gigolo
Miscellaneous Access and VB "stuff" at www.ScoBiz.com
 
V

Van T. Dinh

Yep. I am aware of Bankers' Rounding.

Would anyone care about 1 cent except bankers?

For me, there is no normal rounding of the as I been through all 3 different
methods of rounding of the to-be-rounded 5 (down, bankers' and up) in
different places and people at each place insisted that they are correct.
 
M

M.L. Sco Scofield

Line up 10 programmers and get 12 answers...

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Useful Metric Conversion #17 of 19: 1 billion billion picolos = 1 gigolo
Miscellaneous Access and VB "stuff" at www.ScoBiz.com
 
V

Van T. Dinh

Sorry, the text got jumbled up somehow. What I wrote was:

For me, there is no normal rounding of the as I have been through all 3
different
methods of rounding of the to-be-rounded 5 (down, bankers' and up) in 3
different places and people at each place insisted that they are correct.

From the mathematician's view point: an evil that should be avoided (like
LookUp Fields). That why we have fractions, surds, PI, etc...
 
D

drumette

The data type is number. For the field I tried single and double. Format is
percent, no decimal places. In query, I have a unit cost ($15.30) minus
discount (5%) giving a unit net cost = 14.54 (2 decimal places). But the
total net cost is calculated on $14.535 which results in an incorrect grand
total.

Thank you.
 
M

M.L. Sco Scofield

LOL! :)

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Useful Metric Conversion #17 of 19: 1 billion billion picolos = 1 gigolo
Miscellaneous Access and VB "stuff" at www.ScoBiz.com
 
Top