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

[Ross]

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,

 

[Damian S]

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.

 

[Ross]

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,

 

[Damian S]

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

 

[Ross]

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

 

[Ross]

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