I don't know what's causing an error in a SUM function expression in the footing section of a report, unless the expression reference is not a table field but rather a calculated field by the RecordSource query?
As you see in the query below, "SumOfDebit" is a calculated field defined in the query.
Code:
SELECT CategoryName, Sum(Debit) AS SumOfDebit
FROM tblCategories INNER JOIN tblRegister ON tblCategories.CatID = tblRegister.CatID
WHERE (((TDate) >= #1/1/2016# And (TDate) <= #10/2/2016#))
GROUP BY CategoryName, tblRegister.CatID
HAVING (((tblRegister.CatID)>0));