Rounding issue

[mattc66 via AccessMonster.com]

Rounding issue.. I have a subform subtotal. On the main form is a text box
total. It is not rounding properly. The total sb 2307.90. However if you take
the amounts out to the 3rd decimal they would add up to 2307.879. How would I
correct this issue?


ExtSalePrice:
1296.38
323.00
14.88
23.38
10.63
21.25
34.00
388.88
195.50

 

[John Vinson]

Rounding issue.. I have a subform subtotal. On the main form is a text box
total. It is not rounding properly. The total sb 2307.90. However if you take
the amounts out to the 3rd decimal they would add up to 2307.879. How would I
correct this issue?

A Currency datatype field is stored with four decimal places (no more,
no fewer). I'd suggest using the Round() function in the calculation
of ExtendedPrice (which you do not show) to round it to two decimals,
prior to adding them up. What's happening is that the ExtendedPrice
field is being calculated with two hidden digits past the second
decimal, and these are throwing your sum off.

John W. Vinson[MVP]

 

[mattc66 via AccessMonster.com]

I added the ROUND function, see below. However now it rounded to the nerest
100.

The Total now reads: 2308.00

Properties
CONTROL SOURCE: =Round(Sum([ExtPrice]))
FORMAT: $#,##0.00;($#,##0.00)
DECIMAL PLACES: 2

 

[Douglas J. Steele]

I believe John meant for you to sum the rounded values, not round the summed
values.

As well, you have to tell the Round function how many places to the right of
the decimal are included in the rounding. The syntax for the Round function
is:

Round(expression [,numdecimalplaces])

If numdecimalplaces is omitted, the function returns integers.

Try:

=Sum(Round([ExtPrice],2))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I added the ROUND function, see below. However now it rounded to the nerest
100.

The Total now reads: 2308.00

Properties
CONTROL SOURCE: =Round(Sum([ExtPrice]))
FORMAT: $#,##0.00;($#,##0.00)
DECIMAL PLACES: 2

A Currency datatype field is stored with four decimal places (no more,
no fewer). I'd suggest using the Round() function in the calculation
of ExtendedPrice (which you do not show) to round it to two decimals,
prior to adding them up. What's happening is that the ExtendedPrice
field is being calculated with two hidden digits past the second
decimal, and these are throwing your sum off.

John W. Vinson[MVP]

 

[John Vinson]

I added the ROUND function, see below. However now it rounded to the nerest
100.

Sorry... I was unclear.

Somewhere, either elsewhere in this query or elsewhere in the
database, you are using some sort of expression to calculate Extended
Price.

THAT EXPRESSION should be using the Round() VBA function - e.g.

Extended Price: Round(<some expression here>, 2)


John W. Vinson[MVP]

 

[mattc66 via AccessMonster.com]

Thank you - that works.

Sorry... I was unclear.

Somewhere, either elsewhere in this query or elsewhere in the
database, you are using some sort of expression to calculate Extended
Price.

THAT EXPRESSION should be using the Round() VBA function - e.g.

Extended Price: Round(<some expression here>, 2)

John W. Vinson[MVP]

 

[onedaywhen]

Somewhere, either elsewhere in this query or elsewhere in the
database, you are using some sort of expression to calculate Extended
Price.

THAT EXPRESSION should be using the Round() VBA function - e.g.

Extended Price: Round(<some expression here>, 2)

If the ROUND() function is used in a query (or any Jet SQL) then Jet's
own ROUND() function will be used, rather than VBA's, no?

Jamie.

--

 

[onedaywhen]

If the ROUND() function is used in a query (or any Jet SQL) then Jet's
own ROUND() function will be used, rather than VBA's, no?

My assumption is based on certain Jet scalar functions behaving
differently from their VBA5 equivalents e.g. IIF():

VBA:
x = 0 : ? IIf(x = 0, -99, 100 / x)

Jet SQL:
SELECT 0 AS x, IIF(x = 0, -99, 100 / x)

The VBA returns an error because both clauses are evaluated but this
does not appear to be the case with the Jet SQL usage.

Jamie.

--