Check box on form that make the Sum in report footer

[Fred's]

Hello to all,

I desperately need your help!

I've created a check box on my form that when I click it, the record
will not appear on the report.
But whatever I do the sum on the report footer remain the same.

What should I do to have the right sum on my report? And please be
specific because I'm not familiar with this type of problem.

Let me know if you need additonal information in order to help,

Thanking all in advance,
Fred's

 

[Ken Sheridan]

It sounds like you are cancelling the record if the value of the field in
question is True. This will hide the records but if you use the Sum function
in the ControlSource of the text box in the footer the value will still be
counted. To overcome this increment the value of the text box in the print
event procedure of the report’s detail section.

Firstly delete the expression in the ControlSource property of the text box
in the footer. Then initialize it to zero in the Print event procedure of
the report header, e.g.

txtGrandTotal = 0

Then increment it with the value of the relevant control in the Print event
procedure of the detail section. If the control is called Amount for
instance:

If PrintCount = 1 Then
txtGrandTotal = txtGrandTotal + Amount
End If

Ken Sheridan
Stafford, England

 

[Fred's]

It sounds like you are cancelling the record if the value of the field in
question is True. This will hide the records but if you use the Sum function
in the ControlSource of the text box in the footer the value will still be
counted. To overcome this increment the value of the text box in the print
event procedure of the report's detail section.

Firstly delete the expression in the ControlSource property of the text box
in the footer. Then initialize it to zero in the Print event procedure of
the report header, e.g.

txtGrandTotal = 0

Then increment it with the value of the relevant control in the Print event
procedure of the detail section. If the control is called Amount for
instance:

If PrintCount = 1 Then
txtGrandTotal = txtGrandTotal + Amount
End If

Ken Sheridan
Stafford, England










- Show quoted text -

Hi Ken,

If I delete the expression in the ControlSource property of the text
box in the footer, my expression from my query will not be good
anymore?
If so, how my units will be calculated?

and what do you mean by :If the control is called Amount for instance,
which control?

Waiting for your answer,

Thank you,
Fred's

 

[Ken Sheridan]

You might need to explain just how you are doing this. Are you summing the
values in the query rather than in a computed control in the footer?
Normally the sum of the values of a field in the detail section would be
obtained by a computed control in the footer with a ControlSource property
along the lines of:

=Sum([Amount])

where amount is the field whose values are being summed. If you are summing
the values in the query, either by means of the DSum function or by a
subquery then you’d need to amend the query so that it sums only those rows
which you haven’t checked. However, I’d recommend that you do the summation
in the report as described above. If the query returns only the unchecked
rows (i.e. with a value of false in the relevant column) and the report
consequently shows only those rows, then summing by means of a computed
control in the footer will automatically return the sum of the values from
just those rows.

Ken Sheridan
Stafford, England

 

[Fred's]

You might need to explain just how you are doing this. Are you summing the
values in the query rather than in a computed control in the footer?
Normally the sum of the values of a field in the detail section would be
obtained by a computed control in the footer with a ControlSource property
along the lines of:

=Sum([Amount])

where amount is the field whose values are being summed. If you are summing
the values in the query, either by means of the DSum function or by a
subquery then you'd need to amend the query so that it sums only those rows
which you haven't checked. However, I'd recommend that you do the summation
in the report as described above. If the query returns only the unchecked
rows (i.e. with a value of false in the relevant column) and the report
consequently shows only those rows, then summing by means of a computed
control in the footer will automatically return the sum of the values from
just those rows.

Ken Sheridan
Stafford, England

Hi Ken,

If I delete the expression in the ControlSource property of the text
box in the footer, my expression from my query will not be good
anymore?
If so, how my units will be calculated?

and what do you mean by :If the control is called Amount for instance,
which control?

Waiting for your answer,

Thank you,
Fred's- Hide quoted text -

- Show quoted text -

Ken, I am summing the values in the query and then add a text boxes in
the report footer and set the record source property of the box to my
query: ie. QuerySum.footwear

Fred's

 

[Ken Sheridan]

Post the SQL of the query here. My guess is that you might be restricting
the result set of the query to those rows where the column in question is
False, but are using a domain aggregation function to sum the values, but
without the necessary criteria to restrict the return value of the function.

Ken Sheridan
Stafford, England

You might need to explain just how you are doing this. Are you summing the
values in the query rather than in a computed control in the footer?
Normally the sum of the values of a field in the detail section would be
obtained by a computed control in the footer with a ControlSource property
along the lines of:

=Sum([Amount])

where amount is the field whose values are being summed. If you are summing
the values in the query, either by means of the DSum function or by a
subquery then you'd need to amend the query so that it sums only those rows
which you haven't checked. However, I'd recommend that you do the summation
in the report as described above. If the query returns only the unchecked
rows (i.e. with a value of false in the relevant column) and the report
consequently shows only those rows, then summing by means of a computed
control in the footer will automatically return the sum of the values from
just those rows.

Ken Sheridan
Stafford, England

On Dec 10, 12:24 pm, Ken Sheridan
It sounds like you are cancelling the record if the value of the field in
question is True. This will hide the records but if you use the Sum function
in the ControlSource of the text box in the footer the value will still be
counted. To overcome this increment the value of the text box in the print
event procedure of the report's detail section.

Firstly delete the expression in the ControlSource property of the text box
in the footer. Then initialize it to zero in the Print event procedure of
the report header, e.g.

txtGrandTotal = 0

Then increment it with the value of the relevant control in the Print event
procedure of the detail section. If the control is called Amount for
instance:

If PrintCount = 1 Then
txtGrandTotal = txtGrandTotal + Amount
End If

Ken Sheridan
Stafford, England

:
Hello to all,

I desperately need your help!

I've created a check box on my form that when I click it, the record
will not appear on the report.
But whatever I do the sum on the report footer remain the same.

What should I do to have the right sum on my report? And please be
specific because I'm not familiar with this type of problem.

Let me know if you need additonal information in order to help,

Thanking all in advance,
Fred's- Hide quoted text -

- Show quoted text -

Hi Ken,

If I delete the expression in the ControlSource property of the text
box in the footer, my expression from my query will not be good
anymore?
If so, how my units will be calculated?

and what do you mean by :If the control is called Amount for instance,
which control?

Waiting for your answer,

Thank you,
Fred's- Hide quoted text -

- Show quoted text -

Ken, I am summing the values in the query and then add a text boxes in
the report footer and set the record source property of the box to my
query: ie. QuerySum.footwear

Fred's