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?

 

[Pat Hartman]

@bmaccess Instead of hardcoding all those sums. Create a subform based on a simple query that uses NO hard coding.

Select ResultPromo, Count(*) AS ResultCount From SomeTable
group by ResultPromo
order by ResultPromo

Also take the advice regarding using arguments instead of hard coding and save yourself an enormous amount of work. Whenever you find yourself suffixing object names, bells should go off. Ding, ding, ding - make work, make work, make work. Stop now!!!

 

[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?

 

[Pat Hartman]

Won't work because ResultPromote is not a field in report RecordSource (I tried it).

This has been the case since the early days of Access for reports. This stupid "feature" now exists also for forms.

In any event, you can do the calc in the query as June suggested OR, you can bind the ResultPromote field to a control in the detail section of the report and make it invisible so you don't have to see it.

 

[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.

 

[Pat Hartman]

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

This feature/bug has been in Access as far back as A97. It is the same feature/bug that forces you to use the report settings to control the report sorting rather than using the RecordSource query. Access rewrites the query to remove any fields not bound to controls. You don't see it immediately. It is only after you save and then go back and add the calculation or reference to the column that is not bound to any controls. I haven't ever bothered to narrow down the timing so I can identify specifically when the code fails. All I remember is it is not while you are testing so it is always a surprise.

 

[Gasman]

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

 

[Pat Hartman]

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

No. Now they act the same. The change to forms is recent. They now act like reports. You cannot reference a column from the RecordSource unless it is bound to a control. But again, you don't see the issue immediately. You only see it once Access has rewritten the query and you open the form/report and try to reference a column not bound to a control.

 

[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?

 

[Pat Hartman]

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

I don't know. I've made sure to put a hidden control on the form when I wanted to reference a control that the users didn't normally see. I run into the issue because I keep an UpdateDT and UpdateBy field on most tables. In the BeforeUpdate event, I set the two values. They are only there to help me to track down errors and so there is no reason to display the fields on the form.

I can't reproduce the error so it may have been fixed.