Rounding

[Guest]

I have a form with 2 subforms in it... Subform A has a record source that is
a totals query. Within the detail section of Subform A the Amount field
correctly displays the currency value, for example 123.45. Within the form
footer I have an expression = sum([Amount])... the result = 123

Subform B has a record source that is not a totals query but has the same
tables as the source. The detail records & sum([amount]) work fine.

I have tried round, changing format of the control, etc. I can't change the
table structure but the field is a number datatype, decimal fieldsize...

Any ideas?

Dave

 

[Allen Browne]

Are you certain you cannot change the field type? There are many bugs in the
way JET handles the Decimal data type. It cannot even perform the most basic
queries reliably:
http://allenbrowne.com/bug-08.html

If you are stuck with the table structure, you might work around the problem
by typecasting it to Double or Currency in your query. Type something like
this into a fresh column in the Field row in query design:
cAmount: CCur(Nz([Amount],0))

In your form, replace all references to Amount to cAmount, e.g.:
=Sum([cAmount])

 

[Guest]

Yes, unfortunately the table is a linked oracle table that is locked down by
the vendor... but your suggestion seems to work, at least I can get the 2
totals to balance out.

Thanks Again, Dave

Are you certain you cannot change the field type? There are many bugs in the
way JET handles the Decimal data type. It cannot even perform the most basic
queries reliably:
http://allenbrowne.com/bug-08.html

If you are stuck with the table structure, you might work around the problem
by typecasting it to Double or Currency in your query. Type something like
this into a fresh column in the Field row in query design:
cAmount: CCur(Nz([Amount],0))

In your form, replace all references to Amount to cAmount, e.g.:
=Sum([cAmount])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form with 2 subforms in it... Subform A has a record source that
is
a totals query. Within the detail section of Subform A the Amount field
correctly displays the currency value, for example 123.45. Within the
form
footer I have an expression = sum([Amount])... the result = 123

Subform B has a record source that is not a totals query but has the same
tables as the source. The detail records & sum([amount]) work fine.

I have tried round, changing format of the control, etc. I can't change
the
table structure but the field is a number datatype, decimal fieldsize...

Any ideas?

Dave