Sum yields #Error in form footer

[LonghronJ]

I'm not sure what is causing this, but I have many forms that sum fields in detail section of the form. From time to time, the sum would yield #Error. I would then manipulate by compacting, deleting the cells, copy from a different form that has no issues, or other actions. For the most part, the sum would then work. However, my luck has run out with this particular form. I basically copy from a form that worked and removed a field in the query and deleted it the same field from the form. See Pics below (one works and one does not). However, whatever I do, I cannot get that #Error to go away. Any ideas on how I can resolve my issues?

[ranman256]

If the fields are set to =Sum(field),
then it should never give Err.

is this what u are using?

[CJ_London]

very confused by your description

but I have many forms that sum fields in detail section of the form

please clarify - summing on a form is usually done in the header or footer

deleting the cells

excel has cells, databases have columns and rows - which is it?

From time to time, the sum would yield #Error

implies the error is actually in one or more of the rows - so any calculations in your rows are suspect

since the problem is to do with calculations, we can't help without seeing those calculations - both for rows and totals

Not sure if it is relevant but if you have ** in your field names, that can cause issues

[LonghronJ]

If the fields are set to =Sum(field),
then it should never give Err.

is this what u are using?

Well, my formula is actually =round(Sum[Field],0), which works in other forms. I just copy the entire row and paste it over. I even recreate a textbox and rewrite the expression. Still no luck.

[LonghronJ]

very confused by your description

please clarify - summing on a form is usually done in the header or footer

excel has cells, databases have columns and rows - which is it?

implies the error is actually in one or more of the rows - so any calculations in your rows are suspect

since the problem is to do with calculations, we can't help without seeing those calculations - both for rows and totals

Not sure if it is relevant but if you have ** in your field names, that can cause issues

Yes, the summing is in the footer of the form.
I am summing rows of data. The images I included above show one form with no issues, and the other with issues. The only difference is that I removed a field, "Category"
There's data in every row, including 0's, so there's no error in any rows.
I do not have any ** in any field names.

[Missinglinq]

The Field being summed in the errant Forms isn't a calculated Field, is it?

Linq ;0)>

[LonghronJ]

The Field being summed in the errant Forms isn't a calculated Field, is it?

Linq ;0)>

The fields are summed. See Image.

[CJ_London]

did your category field have a criteria? If so, it may be that you are now bringing data through which is excluded in the 'working' form - and perhaps there is something wrong with that data...

Why do you have the tblCustomerListReporting table - you are not using it?

Also, small point, but you should be applying the criteria to a WHERE column, not a GROUP BY column. HAVING should only be applied to columns that are aggregated. As structured at the moment, you are bringing all data through and then applying a criteria - so a much slower query.

[pbaldy]

Double check that the sum textboxes are in the form footer, not the page footer.

[LonghronJ]

did your category field have a criteria? If so, it may be that you are now bringing data through which is excluded in the 'working' form - and perhaps there is something wrong with that data...

Why do you have the tblCustomerListReporting table - you are not using it?

Also, small point, but you should be applying the criteria to a WHERE column, not a GROUP BY column. HAVING should only be applied to columns that are aggregated. As structured at the moment, you are bringing all data through and then applying a criteria - so a much slower query.

Yes, the one that works contains the category field. It does have a criteria. As far as I can tell, there's no issue with the data. It's essentially the same data but without the Category field.
tblCustomerListReporting is primarily for additional criteria. See image.

[LonghronJ]

Double check that the sum textboxes are in the form footer, not the page footer.

Yup. The sum textboxes are in the footer. See image.

[kd2017]

I get that error sometimes when I change the names of controls. If I have a control with the same name as a field, then change the name of the control, access changes all the other references to that control instead of the field which screws up footer calcs for some reason. Maybe look into that?

[LonghronJ]

I get that error sometimes when I change the names of controls. If I have a control with the same name as a field, then change the name of the control, access changes all the other references to that control instead of the field which screws up footer calcs for some reason. Maybe look into that?

Just checked. All the controls being summed have the same name as the fields. I did not change any of them.

[pbaldy]

There's a 1 at the end of each field name in the footer.

[LonghronJ]

OK. I just discovered the problem. In the circled field below, I noticed the expression is still referencing the field Category, which I removed, the expression was "=Count([Category])". I changed it to "=Count([BudgetLine])" and now it works. It is strange that since Category is not in the query, upon opening the form, I did not get some kind of error message saying that Category cannot be found. Thank you all for helping.