rounding error issues on invoices

I have a report/invoice that lists volume, price per unit, and extended
price.

My problem is being able to get the extended price by multiplying the
[volume] x [price per unit]. With just 2 decimal places on both volume and
[unit price] I've discovered there is not way to get the exact [extened
price] on invoices with extended price values greater then 50k due to
rounding errors. This customer wants only 2 decimal places on the invoice.

Is there an industry standard for this? The [extended price] must match the
companies internal accounting system so it can't be fiddled with.

What do other companies do in this scenario?

re: rounding error issues on invoices

Have you got some examples? I'd be inclined to store the underlying numbers in more precise fields and limit the display output to 2 decimal places... that should help you get the precision you need when calculating...

--
Message posted via http://www.accessmonster.com

re: rounding error issues on invoices

If you multiply 2 numbers that have 2 decimal places each, you can get 4
places in the result, e.g.:
? .01 * .01
0.0001

Try using an expression like this as the calculated field in your query:
ExtendedPrice: CCur(Round(Nz([volume] * [price per unit],0),2))

That expression:
- handles the nulls (because CCur() barfs on Null);
- rounds the result to 2 places;
- typecasts that result to Currency.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"astro" <astro-puppy@mn.rr.com> wrote in message
news:_nqvd.137151$T02.123316@twister.rdc-kc.rr.com...[color=blue]
>
> I have a report/invoice that lists volume, price per unit, and extended
> price.
>
> My problem is being able to get the extended price by multiplying the
> [volume] x [price per unit]. With just 2 decimal places on both volume
> and
> [unit price] I've discovered there is not way to get the exact [extened
> price] on invoices with extended price values greater then 50k due to
> rounding errors. This customer wants only 2 decimal places on the
> invoice.
>
> Is there an industry standard for this? The [extended price] must match
> the
> companies internal accounting system so it can't be fiddled with.
>
> What do other companies do in this scenario?[/color]

re: rounding error issues on invoices

Thanks folks - I'm not being clear here...

my point is that there is no way to get the [exteneded sum] by using only 2
decimals for the inputs (volume & [unit price]) ...the rounding that a
custom agent's calculator would do would make that impossible.

Here is an example:

volume: 108.88 kg
unit price: 16.4569 ===>> round up to 16.46
invoice [extended price]: 1791.83

value #1 (16.46) yields = [extended price]: (108.88)(16.46) =
- above true price
value #2 (16.45) yields = [extended price]: (108.88)(16.45) =
- below true price

I cannot change the true [extended price]...it must match the accounting
dept's value.

My only choice seem to be extending the precision of [unit price] to 4
decimal places...this yields: (16.4569)(108.88) = 1791.83

Of course this will only work for results below a certain threshold (50k
maybe) ...at which point I would need more decimals to get the exact
[extended price].

What I want to know is this: is there an industry standard for handling
this situation - one standard might be that on shipping invoices the [unit
price] * [quantity] does not have to exactly match the [extended price]...
which is of course not the line i'm getting from my client...

Or maybe shipping invoices with large $$ on them display 6 decimal places to
insure an exact match.....

I don't know -- haven't been here before...wondering if anyone else has
dealt with this before...

Thanks


"astro" <astro-puppy@mn.rr.com> wrote in message
news:_nqvd.137151$T02.123316@twister.rdc-kc.rr.com...[color=blue]
>
> I have a report/invoice that lists volume, price per unit, and extended
> price.
>
> My problem is being able to get the extended price by multiplying the
> [volume] x [price per unit]. With just 2 decimal places on both volume[/color]
and[color=blue]
> [unit price] I've discovered there is not way to get the exact [extened
> price] on invoices with extended price values greater then 50k due to
> rounding errors. This customer wants only 2 decimal places on the[/color]
invoice.[color=blue]
>
> Is there an industry standard for this? The [extended price] must match[/color]
the[color=blue]
> companies internal accounting system so it can't be fiddled with.
>
> What do other companies do in this scenario?
>
>[/color]

re: rounding error issues on invoices

Um, I think you're really talking about business standards, rather than technical standards. I'd have thought that, legally, the visible math has to add up ie. 108.88 * $16.46 = $1792.1648 -> rounds to $1792.16

Some industries have a convention of rounding up or down to the nearest unit of measure they're willing to accept eg. to the nearest dollar, but I'd have thought that woul dneed to be on their terms & conditions somewhere. If you can't change the extended price, then I think you'd have to display the unit price to its full number of decimal places.

--
Message posted via http://www.accessmonster.com

re: rounding error issues on invoices

Yes - that would make sense to put it under the 'terms and conditions' - I
will ask my client about this..

Thanks!


"David Seeto via AccessMonster.com" <forum@AccessMonster.com> wrote in
message news:b18efbc2a1914a8c9887270d6e0cb9f9@AccessMonste r.com...[color=blue]
> Um, I think you're really talking about business standards, rather than[/color]
technical standards. I'd have thought that, legally, the visible math has to
add up ie. 108.88 * $16.46 = $1792.1648 -> rounds to $1792.16[color=blue]
>
> Some industries have a convention of rounding up or down to the nearest[/color]
unit of measure they're willing to accept eg. to the nearest dollar, but I'd
have thought that woul dneed to be on their terms & conditions somewhere. If
you can't change the extended price, then I think you'd have to display the
unit price to its full number of decimal places.[color=blue]
>
> --
> Message posted via http://www.accessmonster.com[/color]