Totals problem with subform

Hi

I am having problems totalling a particular field in a subform.I am
using Access 2003. The subform is based on a query which selects
records with various financial information. I have 5 text boxes in the
form footer which total certain fields. I have no problem totalling 4
of the fields, but one field which is a calculated field ( ie gross
cash - commisions= nett) rounds the figure down ie 175.85 becomes
175.00... I have tried everything to correct this..I have checked the
tables and the query to ensure that the field is formatted and the
query returns the correct value when it is run, however the total is
always incorrect...would be grateful for any advice on this...thanks

Steve

Your table fields used in the calculated control - [gross cash] and
[commissions] must be data type of either decimal, currency or single. If
one or both is an integer, then you will always get whole numbers, no matter
how many decimal places are set in the format properties.
-Ed

"teve_s" <stevesutherland@blueyonder.co.ukwrote in message
news:1157369567.764156.30430@i3g2000cwc.googlegrou ps.com...

Quote:

Hi
>
I am having problems totalling a particular field in a subform.I am
using Access 2003. The subform is based on a query which selects
records with various financial information. I have 5 text boxes in the
form footer which total certain fields. I have no problem totalling 4
of the fields, but one field which is a calculated field ( ie gross
cash - commisions= nett) rounds the figure down ie 175.85 becomes
175.00... I have tried everything to correct this..I have checked the
tables and the query to ensure that the field is formatted and the
query returns the correct value when it is run, however the total is
always incorrect...would be grateful for any advice on this...thanks
>
>
Steve
>

Hi Ed

Thanks for your response. The table fields are decimal and the actual
query returns the correctly calculated figure for each record...it is
only the totals text box in the footer of the subform which is shows
the incorrcet toatl figure...any other ideas?

Regards

Steve

Ed Robichaud wrote:

Quote:

Your table fields used in the calculated control - [gross cash] and
[commissions] must be data type of either decimal, currency or single. If
one or both is an integer, then you will always get whole numbers, no matter
how many decimal places are set in the format properties.
-Ed
>
"teve_s" <stevesutherland@blueyonder.co.ukwrote in message
news:1157369567.764156.30430@i3g2000cwc.googlegrou ps.com...

Quote:

Hi

I am having problems totalling a particular field in a subform.I am
using Access 2003. The subform is based on a query which selects
records with various financial information. I have 5 text boxes in the
form footer which total certain fields. I have no problem totalling 4
of the fields, but one field which is a calculated field ( ie gross
cash - commisions= nett) rounds the figure down ie 175.85 becomes
175.00... I have tried everything to correct this..I have checked the
tables and the query to ensure that the field is formatted and the
query returns the correct value when it is run, however the total is
always incorrect...would be grateful for any advice on this...thanks

Steve

Make sure that the format property of your calculated control and/or query
field is "standard" and decimal places set to "2".

Also the "decimal" field type has been know to cause errors. Try changing
it to "single" or "double".
-Ed

"Steve_s" <stevesutherland@blueyonder.co.ukwrote in message
news:1157381527.179574.118390@p79g2000cwp.googlegr oups.com...

Quote:

Hi Ed
>
Thanks for your response. The table fields are decimal and the actual
query returns the correctly calculated figure for each record...it is
only the totals text box in the footer of the subform which is shows
the incorrcet toatl figure...any other ideas?
>
>
Regards
>
Steve
>
>
>
Ed Robichaud wrote:

Quote:

>Your table fields used in the calculated control - [gross cash] and
>[commissions] must be data type of either decimal, currency or single.
>If
>one or both is an integer, then you will always get whole numbers, no
>matter
>how many decimal places are set in the format properties.
>-Ed
>>
>"teve_s" <stevesutherland@blueyonder.co.ukwrote in message
>news:1157369567.764156.30430@i3g2000cwc.googlegro ups.com...

Quote:

Hi
>
I am having problems totalling a particular field in a subform.I am
using Access 2003. The subform is based on a query which selects
records with various financial information. I have 5 text boxes in the
form footer which total certain fields. I have no problem totalling 4
of the fields, but one field which is a calculated field ( ie gross
cash - commisions= nett) rounds the figure down ie 175.85 becomes
175.00... I have tried everything to correct this..I have checked the
tables and the query to ensure that the field is formatted and the
query returns the correct value when it is run, however the total is
always incorrect...would be grateful for any advice on this...thanks
>
>
Steve
>

>

Hi Ed

Changed the source tables data type to double and the problem
disappeared!...thanks for your help..very much appreciated..I have
almost the same database which I used in access 2000 with similar
calculated fields and did not have any issues , maybe this is an access
2003 specific issue...thanks again

Regards

Steve

Ed Robichaud wrote:

Quote:

Make sure that the format property of your calculated control and/or query
field is "standard" and decimal places set to "2".
>
Also the "decimal" field type has been know to cause errors. Try changing
it to "single" or "double".
-Ed
>
"Steve_s" <stevesutherland@blueyonder.co.ukwrote in message
news:1157381527.179574.118390@p79g2000cwp.googlegr oups.com...

Quote:

Hi Ed

Thanks for your response. The table fields are decimal and the actual
query returns the correctly calculated figure for each record...it is
only the totals text box in the footer of the subform which is shows
the incorrcet toatl figure...any other ideas?

Regards

Steve

Ed Robichaud wrote:

Quote:

Your table fields used in the calculated control - [gross cash] and
[commissions] must be data type of either decimal, currency or single.
If
one or both is an integer, then you will always get whole numbers, no
matter
how many decimal places are set in the format properties.
-Ed
>
"teve_s" <stevesutherland@blueyonder.co.ukwrote in message
news:1157369567.764156.30430@i3g2000cwc.googlegrou ps.com...
Hi

I am having problems totalling a particular field in a subform.I am
using Access 2003. The subform is based on a query which selects
records with various financial information. I have 5 text boxes in the
form footer which total certain fields. I have no problem totalling 4
of the fields, but one field which is a calculated field ( ie gross
cash - commisions= nett) rounds the figure down ie 175.85 becomes
175.00... I have tried everything to correct this..I have checked the
tables and the query to ensure that the field is formatted and the
query returns the correct value when it is run, however the total is
always incorrect...would be grateful for any advice on this...thanks

Steve

Totals problem with subform

re: Totals problem with subform

re: Totals problem with subform

re: Totals problem with subform

re: Totals problem with subform

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about