Enter Parameter Value? I cannot seem to use sum in footer. my field or control not found. Need help please

[bmaccess]

This is in a txtbox ResultPromote in the detail section of a REPORT

=IIf([total subjects]<7,"U",IIf(([IsiXhosa Home Language]>=40 Or [English Home Language]>=40 Or [Afrikaans Home Language]>=40) And [AmountSubjectsFail]<2,"P","NP"))



I need to count each of the conditions. These are placed in my footer section

I placed these in txtboxes respectively.

txtTotalP:

=Sum(IIf([ResultPromote ]="P",1,0))



txtTotalNP:

=Sum(IIf([ResultPromote ]="NP",1,0))



txtTotalU:

=Sum(IIf([ResultPromote ]="U",1,0))



When I run my report the dialog pops up enter value parameter ResultPromote.

 

[sonic8]

=Sum(IIf([ResultPromote ]="U",1,0))

All your references to ResultPromote have a trailing space inside the square brackets. The square brackets make this space part of the Name that is being searched for. This control cannot be found and thus the error is raised. I believe, you cannot (and more importantly, absolutely should not!) name a control with a trailing space.

 

[Gasman]

Plus, if no spaces in the control name, no [] needed?

 

[bmaccess]

All your references to ResultPromote have a trailing space inside the square brackets. The square brackets make this space part of the Name that is being searched for. This control cannot be found and thus the error is raised. I believe, you cannot (and more importantly, absolutely should not!) name a control with a trailing space.

There were no trailing space. When I insert name I left a space. I checked. You can see in image. Still same message appear

 

[Gasman]

Not relevant to your question, but all those reports with a suffix, raise warning bells.?
Have you thought of one report and criteria for the data? Then when you need to modify a report like you are doing now, you do it once not 4 times?

Always copy and post what you have. Do not retype and make mistakes like that.

Upload enough of the DB to see the issue.

 

[Gasman]

@GPGeorge Unable to reply, getting an Oops message, but there appears to be no trailing space in the picture.

Could remove the [] as well?

 

[bmaccess]

Hi there. I know you all mean well. I will sort out my database again. But for now I just would like to solve that small little issue.
Any other suggestions how to solve the problem. Much appreciated.

 

[June7]

Won't work because ResultPromote is not a field in report RecordSource (I tried it). Do ResultPromote calc in query and use query as RecordSource. Then reference field in Sum expression. This works.

Quite possibly cannot do this calc in CROSSTAB query and will have to build another query that uses CROSSTAB.

I think applying filter to CROSSTAB (or query based on CROSSTAB) requires using PARAMETERS or specifying column headings. Review http://allenbrowne.com/ser-67.html.

Gasman, Access will add the [].

 

[Gasman]

Would removing the[ ] solve that?

 

[June7]

Would removing the[ ] solve that?

No. Including [] never hurts but not including can. As I said, Access will often add them in anyway (in queries and textboxes, not VBA).

 

[bmaccess]

Thanks for your help. Will try that.

 

[June7]

Pat, calc I suggested doing in query is ResultPromote value, not calcs using ResultPromote.

In my test with Access 2010, ResultPromote did not have to be in textbox on report.

 

[Gasman]

And yet I seem to recall you said the opposite happens on forms?

 

[June7]

Now I did a test that saved report/form with expression referencing field not bound to textbox. Still works for both in Access 2010 and 2021.

I do remember this being an issue with Access 2003 and maybe also 2007 for reports but it changed, possibly with 2010.

So if it's back, it's after Access 2021. Could this now be a "feature" of 365 only?