By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 2,606 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Totals problem with subform

P: n/a
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

Sep 4 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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" <st*************@blueyonder.co.ukwrote in message
news:11*********************@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

Sep 4 '06 #2

P: n/a
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:
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" <st*************@blueyonder.co.ukwrote in message
news:11*********************@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
Sep 4 '06 #3

P: n/a
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" <st*************@blueyonder.co.ukwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...
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:
>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" <st*************@blueyonder.co.ukwrote in message
news:11*********************@i3g2000cwc.googlegro ups.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

Sep 4 '06 #4

P: n/a
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:
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" <st*************@blueyonder.co.ukwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...
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:
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" <st*************@blueyonder.co.ukwrote in message
news:11*********************@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
Sep 4 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.