rounding and reporting values 1

[turnerjs]

MS Access 97 - I have to calculate 2 tax amounts based on a subtotal amout.

In Query (Total = subtotal + tax 1 + tax 2)

subtotal = 25.3333
tax 1 = 50.333
tax 2 = 25.3333
Total = 100.9996

When I print the individual fields on an invoice or display in a form as 2 decimals, I get

subtotal = 25.33
tax 1 = 50.33
tax 2 = 25.33

Total = 101.00

The 3 detail fields do not add up to the total field.

Is there a (simple) solution to stop the rounding.
I would prefer the detail fields were limitied to 2 decimals

When defining the Table fields -

if I set field to double, the decimal positions is for display purposes only.
Integer does not allow decimals, even though there is no error when you define 2 decimals.


Interger does not allow decimals.

 

[Maquis]

Yikes! Remind never to move to wherever you're located. You're taxes are horrendous! :-0

Try setting the field types to currency. This should round all the dollar amounts (items, taxes and totals) to 2 decimal places appropiately. Maq B-)
<insert witty signature here>

 

[turnerjs]

Thanks for the tip, however

I set the Tables fields to currancy, Fixed and 2 decimals
same results. The query shows 2 decimals, but when you open the field there are more decimals present.

I even tried setting the field properties in query to currancy, same problems.

The Form's total of the 3 individual fields does not add up to the invoice total amount (out by .01)

 

[Maquis]

Well, one trick I've used in the past would be
total = subtotal + tax1 + tax2 - .005

This would guarantee that the rounding is always rounded down. There may be a better way through some pre-defined Access function, but I don't know what function it would be off the top of my head.

Just curious, why are your users entering prices with more than 2 decimal places to begin with? The taxes I can understand since they're calculated, but not the sub-total. Maq B-)
<insert witty signature here>

 

[turnerjs]

Thanks Maquis,

The subtracting of .005, in this case worked fine (I added a 'iif' zero test).

My use of subtotal with > 2 decimals was a poor example. It should always have 2 decimals.

I have other instances were I need to accumulate more than 2 tax fields - a tax may or may not be calculated per detail line and I will have more than 2 detail lines.

If anyone knows of the final solution, please let me know.

Thanks for the help.

 

[turnerjs]

Maquis and everyone else

The following function seems to work

Total: CDbl(Format([Subtotal],&quot;0.00&quot)+CDbl(Format([PST],&quot;0.00&quot)+CDbl(Format([GST],&quot;0.00&quot)

Total is a numeric field. The Format function seems to return only to a String, therefore we used the CDBL to convert the String to a 'double' number.

Thanks Yao Wang