I apologise if this question has been answered elsewhere but I have tried looking and can't find anything that helps.
I'm in the process of upsizing an access database to an adp front end and a sql server back end. One of the forms in my original database was a continuous form with some calculated text boxes in the form footer. One for example had the formula
which worked quite happilly.
I've now changed my forms recordsource to a stored procedure which works fine, however none of the calculated text boxes in the footer of the form work. I have read that complex expressions might not work in adp's and that I should use CASE instead of IIF, which I've done. I've taken the iif part of the formula and added a calculated field to my underlying query so all I need to do in the calculated text box is an =sum([aField]) but I still get a #Error.
Even if I do a =count(1) or something really simple like that I still get #Error.
Can anyone see where I'm going wrong?
I'm in the process of upsizing an access database to an adp front end and a sql server back end. One of the forms in my original database was a continuous form with some calculated text boxes in the form footer. One for example had the formula
Code:
=Sum(IIf([field1]="valueA",1,0))
I've now changed my forms recordsource to a stored procedure which works fine, however none of the calculated text boxes in the footer of the form work. I have read that complex expressions might not work in adp's and that I should use CASE instead of IIF, which I've done. I've taken the iif part of the formula and added a calculated field to my underlying query so all I need to do in the calculated text box is an =sum([aField]) but I still get a #Error.
Even if I do a =count(1) or something really simple like that I still get #Error.
Can anyone see where I'm going wrong?