Totals Row Not Summing

[Matthew Pfluger]

My database contains several parent-child relationships. On the main form, I
display two subforms: one of a datasheet of parents and their total hours and
costs, and one of the children of the selected parent and its total hours and
cost. I enabled the totals row on the two datasheet subforms, but the totals
row doesn't display the SUM of the column. The source for the subforms are
two queries that contain Nz() functions to fill in blank cells with 0's.

I tried setting the total row to other functions like COUNT and VARIANCE,
and every other function works. Why would every other function work but not
SUM?

Thanks,
Matthew Pfluger

 

[Klatuu]

Is the calculated control that should show the sum on the subform's footer?

Let's say your subform text box control is txtCost and it's ControlSource is
[ItemCost]
In the subform's footer say you have a textbox named txtTotCost. It's
Control Source should be:
=Sum([ItemCost])

Now, on your main form, you will need a control to display the value because
the control on the subform footer in datasheet view will not be visible. Its
control source should be:

=SubformControlName.Form.txtTotCost

 

[Matthew Pfluger]

It is not a calculated coltrol that is showing the sum, it is the datasheet's
summary row. You know, you click the "Totals" switch on the Ribbon, and the
summary ro appears at the bottom of the datasheet. I try to set the summary
to SUM, but it doesn't calculate a sum. There are no blanks in the column.
Also, I tried wrapping the function in the source query with a
CLng(Nz([field],0)), but that also did not make the SUM function work.

Any suggestions? I'm out of ideas. The summary row works in the source
query, so I'm not sure why it fails in the subform.

Thanks,
Matthew Pfluger

Is the calculated control that should show the sum on the subform's footer?

Let's say your subform text box control is txtCost and it's ControlSource is
[ItemCost]
In the subform's footer say you have a textbox named txtTotCost. It's
Control Source should be:
=Sum([ItemCost])

Now, on your main form, you will need a control to display the value because
the control on the subform footer in datasheet view will not be visible. Its
control source should be:

=SubformControlName.Form.txtTotCost
--
Dave Hargis, Microsoft Access MVP

My database contains several parent-child relationships. On the main form, I
display two subforms: one of a datasheet of parents and their total hours and
costs, and one of the children of the selected parent and its total hours and
cost. I enabled the totals row on the two datasheet subforms, but the totals
row doesn't display the SUM of the column. The source for the subforms are
two queries that contain Nz() functions to fill in blank cells with 0's.

I tried setting the total row to other functions like COUNT and VARIANCE,
and every other function works. Why would every other function work but not
SUM?

Thanks,
Matthew Pfluger

 

[Klatuu]

When you are posting, it is a good idea to identify the version you are using.
Sorry, but I don't have 2007 here and have not used that feature yet, so I
really can't help.

I was describing how you would do it in 2003.
--
Dave Hargis, Microsoft Access MVP

It is not a calculated coltrol that is showing the sum, it is the datasheet's
summary row. You know, you click the "Totals" switch on the Ribbon, and the
summary ro appears at the bottom of the datasheet. I try to set the summary
to SUM, but it doesn't calculate a sum. There are no blanks in the column.
Also, I tried wrapping the function in the source query with a
CLng(Nz([field],0)), but that also did not make the SUM function work.

Any suggestions? I'm out of ideas. The summary row works in the source
query, so I'm not sure why it fails in the subform.

Thanks,
Matthew Pfluger

Is the calculated control that should show the sum on the subform's footer?

Let's say your subform text box control is txtCost and it's ControlSource is
[ItemCost]
In the subform's footer say you have a textbox named txtTotCost. It's
Control Source should be:
=Sum([ItemCost])

Now, on your main form, you will need a control to display the value because
the control on the subform footer in datasheet view will not be visible. Its
control source should be:

=SubformControlName.Form.txtTotCost
--
Dave Hargis, Microsoft Access MVP

My database contains several parent-child relationships. On the main form, I
display two subforms: one of a datasheet of parents and their total hours and
costs, and one of the children of the selected parent and its total hours and
cost. I enabled the totals row on the two datasheet subforms, but the totals
row doesn't display the SUM of the column. The source for the subforms are
two queries that contain Nz() functions to fill in blank cells with 0's.

I tried setting the total row to other functions like COUNT and VARIANCE,
and every other function works. Why would every other function work but not
SUM?

Thanks,
Matthew Pfluger