"Total" control is rounding decimal places to .00 on a Form

[Guest]

Hi all~Hope you can help.

The facts first:
1. The underlying query for my subform has a calculated field called
Extended Cost, which is mileage * 0.345 + Cost. This calculates correctly
for each row in the query and the subform.
2. The subform (called Plan Details) footer has a control called Invoice
Seller which is set to =Sum([Extended Cost])
3. The main form has a Total control set as [Plan Details].[Form]![Invoice
Seller]

Problem: The amount showing for the Total is either not picking up the
decimal places from the subform control, or it's rounding down to .00. Is
this because it doesn't like multiplying by decimals, or does it just need
some kind of special formatting?

Any help from you all would be appreciated, because it's above me at the
moment..

Thanks,

 

[Guest]

Hi Ross,

Try checking the format you have specified for that field. IF it's
calculating correctly, it must be the formatting that is the issue.

Damian.

 

[Guest]

Hi Damian,

I've tried formatting it several ways - decimal w/2 places, decimal w/auto,
currency w/2 places, currency w/auto - doesn't seem to make a difference.

--
smither fan

Hi Ross,

Try checking the format you have specified for that field. IF it's
calculating correctly, it must be the formatting that is the issue.

Damian.

Hi all~Hope you can help.

The facts first:
1. The underlying query for my subform has a calculated field called
Extended Cost, which is mileage * 0.345 + Cost. This calculates correctly
for each row in the query and the subform.
2. The subform (called Plan Details) footer has a control called Invoice
Seller which is set to =Sum([Extended Cost])
3. The main form has a Total control set as [Plan Details].[Form]![Invoice
Seller]

Problem: The amount showing for the Total is either not picking up the
decimal places from the subform control, or it's rounding down to .00. Is
this because it doesn't like multiplying by decimals, or does it just need
some kind of special formatting?

Any help from you all would be appreciated, because it's above me at the
moment..

Thanks,

 

[Guest]

Strange... you aren't using a round function anywhere are you? What about
if you create a new control and put the same sum() code in it, does it still
format strangely?

Damian.

Hi Damian,

I've tried formatting it several ways - decimal w/2 places, decimal w/auto,
currency w/2 places, currency w/auto - doesn't seem to make a difference.

--
smither fan

Hi Ross,

Try checking the format you have specified for that field. IF it's
calculating correctly, it must be the formatting that is the issue.

Damian.

Hi all~Hope you can help.

The facts first:
1. The underlying query for my subform has a calculated field called
Extended Cost, which is mileage * 0.345 + Cost. This calculates correctly
for each row in the query and the subform.
2. The subform (called Plan Details) footer has a control called Invoice
Seller which is set to =Sum([Extended Cost])
3. The main form has a Total control set as [Plan Details].[Form]![Invoice
Seller]

Problem: The amount showing for the Total is either not picking up the
decimal places from the subform control, or it's rounding down to .00. Is
this because it doesn't like multiplying by decimals, or does it just need
some kind of special formatting?

Any help from you all would be appreciated, because it's above me at the
moment..

Thanks,

 

[JK]

Ross,

It is not clear what you mean by "The amount showing for the Total ", which
total? the calculated field in the subForm or your "Total Control" on you
main form. If it is the later *and* the total control is a field in a table,
check the date type of table, it may be set to Long Integer (the default) -
if this is the case change it to Double *and* update the table.

Otherwise, at the risk of stating the obvious, have you tried to do the sum
manually? your Sum() function will *always* return the correct result. If
Mileage is expressed in *whole* thousands (25,000 37,000 etc) and your Cost
in "+Cost" is a whole number the extended cost and consequently the Sum()
will alway be a whole number.

Regards/JK

 

[Guest]

Hi JK

The calculation is from calculated field in a query, not from a table. I've
been able to verify that the query calculation is working correctly, however,
the subform control with the sum function, isn't. The query calculation is
the mileage *0.345 + the cost, mileage and cost being fields the the query
and also in the subform. The subform 'sum' control is dropping the zeros.
For instance, in one record the 'Total' control on the mainform should show
$55.89, which is the sum of 3 items (rows), in the subform within the same
record, but is only showing $55.00.

For some reason, the subform footer control can't sum the rows together, or
if it is, the mainform 'Total' control isn't picking up the cents. I've
about given up. Tried everything as far as formatting that I can think of.
Is there maybe format that I need to enter into the input mask for the
'Total' control on the mainform?

Thanks for taking the time out to help..
--
smither fan

Ross,

It is not clear what you mean by "The amount showing for the Total ", which
total? the calculated field in the subForm or your "Total Control" on you
main form. If it is the later *and* the total control is a field in a table,
check the date type of table, it may be set to Long Integer (the default) -
if this is the case change it to Double *and* update the table.

Otherwise, at the risk of stating the obvious, have you tried to do the sum
manually? your Sum() function will *always* return the correct result. If
Mileage is expressed in *whole* thousands (25,000 37,000 etc) and your Cost
in "+Cost" is a whole number the extended cost and consequently the Sum()
will alway be a whole number.

Regards/JK

Hi all~Hope you can help.

The facts first:
1. The underlying query for my subform has a calculated field called
Extended Cost, which is mileage * 0.345 + Cost. This calculates correctly
for each row in the query and the subform.
2. The subform (called Plan Details) footer has a control called Invoice
Seller which is set to =Sum([Extended Cost])
3. The main form has a Total control set as [Plan Details].[Form]![Invoice
Seller]

Problem: The amount showing for the Total is either not picking up the
decimal places from the subform control, or it's rounding down to .00. Is
this because it doesn't like multiplying by decimals, or does it just need
some kind of special formatting?

Any help from you all would be appreciated, because it's above me at the
moment..

Thanks,

 

[JK]

Hi Ross,

You got me baffled.

I do similar things quite often and have never encountered that problem.
When a calculation such as yours comes from a query, one would expect to get
more then 2 decimal unless you limit the calculation by Round() function
and/or by the format. I tried to replicate your problem to avail.

I is difficult to do it in remote control but a here are few suggestions:

Remove the format from the field altogether, remove the mask (it should
*not* be there in any event), set decimal to Auto, check that you don't have
any of those set in VB, if so remove them. See if that gives you the
decimals. If so, just use the format property

The solution must be there staring at you. Sometime it is better just to
walk away from a problem and come back to it the next day with a clear mind
and bingo.

Alternatively, I'll be happy to have a look at you db, if you wish me to. If
so Zip it to:
sarichart at ozzienet dot net *and* put something like "Ross DB" to enable
me to distinguish it from a spam.

Regards
Jacob

 

[Guest]

Hi Jacob,

I know...it's ridiculous. It would seem to be an everyday type of
calculation and shouldn't be causing such a problem. There is nothing in
input mask. Just thought maybe it needed something.

I'm going to pursue it a little more at my end before I take you up on your
offer, but I may send it to you next week.

Thanks again..
--
smither fan

Hi Ross,

You got me baffled.

I do similar things quite often and have never encountered that problem.
When a calculation such as yours comes from a query, one would expect to get
more then 2 decimal unless you limit the calculation by Round() function
and/or by the format. I tried to replicate your problem to avail.

I is difficult to do it in remote control but a here are few suggestions:

Remove the format from the field altogether, remove the mask (it should
*not* be there in any event), set decimal to Auto, check that you don't have
any of those set in VB, if so remove them. See if that gives you the
decimals. If so, just use the format property

The solution must be there staring at you. Sometime it is better just to
walk away from a problem and come back to it the next day with a clear mind
and bingo.

Alternatively, I'll be happy to have a look at you db, if you wish me to. If
so Zip it to:
sarichart at ozzienet dot net *and* put something like "Ross DB" to enable
me to distinguish it from a spam.

Regards
Jacob

 

[JK]

Hi Ross,

You might just as well walk into the problem next Monday with a clear mind
and .. gotcha ...
Failing that my offer stands.

Regards
Jacob

Hi Jacob,

I know...it's ridiculous. It would seem to be an everyday type of
calculation and shouldn't be causing such a problem. There is nothing in
input mask. Just thought maybe it needed something.

I'm going to pursue it a little more at my end before I take you up on
your
offer, but I may send it to you next week.

Thanks again..

[/QUOTE]

 

[Guest]

Hi,

Figured it out. The calculation in the query for the subform needed to be
Extended Cost: CCur([Mileage]*0.345+[Cost]). I guess the 'CCur' part forces
the currency setting? Also, I found that it didn't work if I tried to
include that in the subform 'sum' control. I'm new, so I figured that just
setting the field to Currency w/2 decimals would take care of it...silly me!
I suppose that would be way too simple.


Anyway, your time and the offer were much appreciated.

Thanks
--
smither fan

Hi Ross,

You might just as well walk into the problem next Monday with a clear mind
and .. gotcha ...
Failing that my offer stands.

Regards
Jacob

[/QUOTE]

 

[JK]

Hi Ross,

Terrific!

CCur() ?, until this moment I did not even know about it ;-/ but nor would
I need it now or ever in the future. I *never* use currency format, It had
caused me problems a few years back so I simply dumped it. In fact I don't
even know why it there at all. It does *nothing* apart from sticking the
currency symbol in front a number *and* causing problems as we have just
found out.

I am a great believer in the "KISS" principle (Keep It Simple Stupid), I
work with numbers! if the number is money and it always the same currency,
the currency sign is not required, definately not in froms. Everybody
understand he 23.25 means $23.25 - when I invoice and required to show the
Dollar sign, I simply use it in the format for display only e.g.

"$"#,##0.00

If I may let out some steam on the subject:

The currency format/function does notinhg more then sitck a dollar sign in
fornt of your number which is taken from the regional settings of Windows
and, unless you limit it, you will get 4 (?) decimal points - Have you ever
recived a bill for $225.2345?

If you work with forgien currencies, forget about it (Currency Format), you
wil, have to work with numbres, limit (round) the decinal point and stick
the currency sign in front - the only way it can be done is by having a
Currencies table with the relevant information, including relevant decimal
points

Hi,

Figured it out. The calculation in the query for the subform needed to be
Extended Cost: CCur([Mileage]*0.345+[Cost]). I guess the 'CCur' part
forces
the currency setting? Also, I found that it didn't work if I tried to
include that in the subform 'sum' control. I'm new, so I figured that
just
setting the field to Currency w/2 decimals would take care of it...silly
me!
I suppose that would be way too simple.


Anyway, your time and the offer were much appreciated.

Thanks
--
smither fan

Hi Ross,

You might just as well walk into the problem next Monday with a clear
mind
and .. gotcha ...
Failing that my offer stands.

Regards
Jacob

 

[Jamie Collins]

CCur() ?, until this moment I did not even know about it ;-/ but nor would
I need it now or ever in the future. I *never* use currency format, It had
caused me problems a few years back so I simply dumped it.

What problems were these? Please share your experiences.

In fact I don't
even know why it there at all. It does *nothing* apart from sticking the
currency symbol in front a number *and* causing problems

In Jet 3.n (and earlier) CURRENCY was the only scaled integer type; the
floating point types are not suitable (possibly illegal) for financial
applications due their inaccurate/inexact nature.

From Jet 4.0 onwards we have had the DECIMAL type: more flexible in

terms of precision/scale, different rounding rules, etc.

I don't use CURRENCY myself for various reasons (portability, rounding
rules, etc) but primarily because DECIMAL is now available: if it were
not, I would probably use CURRENCY for reasons of accuracy.

....who am I kidding?! I'd use another SQL platform <vbg>.

Jamie.

--

 

[Jamie Collins]

Hi,

Figured it out. The calculation in the query for the subform needed to be
Extended Cost: CCur([Mileage]*0.345+[Cost]).

FWIW operating on a CURRENCY value using a decimal literal in SQL
coerces the result to the DECIMAL type e.g.

SELECT TYPENAME(CCUR(100) * 0.345)

returns 'Decimal'.

Jamie.

--