Cannot Sum form field in form footer

[Steve_b]

I have a form that is based on a query that contains data created by a crosstab query. In the detail section I can create a calculated field using the form fields. When I use the footer to sum the same fields I get an ERROR. I am using the sum function, =sum([formfield1]) which a numeric.



I have used sum before in my forms. The only thing that is different is that query has data generated a cross tab query. Any advice is greatly appreciated!

 

[MajP]

This may be of help on how to sum calculated fields. 3 approaches are provided.
http://www.jegsworks.com/lessons/databases/formsreports/step-totals-calculated.htm

 

[Steve_b]

Thanks for the reference. After studying it, I had no luck. Here is the SQL code from this query and what the resulting query looks like. I am trying to sum the fields 2019-2020 and 2019-2020 which derive from a form field, so the name will change based on the year span selected. I can easily do this in a report but I would like this in a form as it a handy reference when working on this application. Thanks for any inspiration!



School Name 2018-2019 2019-2020

Edison 54 62

and so on for 8 other schools

SELECT ftecrosstab.trschool_id, schools.schoollName, schools.Type, ftecrosstab.[2018-2019] AS Expr1, ftecrosstab.[2019-2020] AS Expr2, [forms]![schools1]![teacherresources1]![combocurrentyear] AS [current], [forms]![schools1]![teacherresources1]![comboproposedyear] AS proposed, schools.close_date
FROM schools INNER JOIN ftecrosstab ON schools.school_id = ftecrosstab.trschool_id
GROUP BY ftecrosstab.trschool_id, schools.schoollName, schools.Type, ftecrosstab.[2018-2019], ftecrosstab.[2019-2020], [forms]![schools1]![teacherresources1]![combocurrentyear], [forms]![schools1]![teacherresources1]![comboproposedyear], schools.close_date
HAVING (((schools.close_date)>[forms]![schools1]![teacherresources1]![combocurrentyear]));

 

[June7]

Building a crosstab to run perpetually is not simple. http://allenbrowne.com/ser-67.html

I was able to build a form with CROSSTAB query as RecordSource and then a textbox in form footer references column header generated by CROSSTAB.

Did not build another SELECT query.