I have a continuous form based on a query using 2 joined tables with a date criteria. Not a problem with this. In the form footer, I have a text box where I compute several totals using the sum function. Again, this is not the problem.
The Problem: I would like to compute several totals similar to the one above but include only the records where a Yes/No field is false then put the results in text boxes, also in the form footer. I tried to use the sum function with a WHERE clause and with a HAVING clause but Access won’t accept it. I built a SQL statement just for that control and put it in the record source property of the text box that was to hold that total but Access mangled it (I tried several times and the result in the property sheet was only part of the query I typed in.
Is there a way that I can do this? If anyone needs the SQL and/or the VBA, I’ll be glad to post it.
Before I read the responses, I decided to create a querydef using a SQL in VBA and then used the querydef as the recordsource for the form rather than using the SQL statement directly.
Then I created a second SQL to sum the columns where the checkbox was false and created a recordset using the second SQL and posted the result into the text boxes (me!txtBox = rst.whateversum). It works and I’ll be able to understand it later if I make a change or something. Thanks for the help. As soon as I have time, I want to try summing with the iif function.