Use queries in reports

[Rabbit]

Have I missed something or can you not use:
[Code=vb]
=DLookUp("Count OfSkillLevel"," qrySkillsWF","S killLevel = 'Beginner' AND Program = '" & [Program] & "'")
[/Code]

[NeoPa]

If you check out post #20 Rabbit, you'll see why I don't recommend that approach, although you certainly can get a correct result that way.
@Beacon,
I've discovered (was told by Mary actually) that the aggregate functions can actually only work on the underlying fields (from the recordset) rather than on any controls in the report per se.
This means that to produce the results we need you will need to update the design of the recordset to include the extra three fields which each contain a 1 if, and only if, the Skill Level matches. Does this make sense?

[beacon]

Are you saying that I need to include different text boxes in the details section of my report?

[beacon]

I went ahead and used what Rabbit suggested and I'm going to use that. I don't think the users of this db are ever going to use enough data that using the dlookup will compromise anything.

I'm going to call this issue complete.

Thank you Nico, NeoPa, and Rabbit for all of your help...and I'm sorry for any headache this might have caused considering that the report's design was flawed from the get-go.

[NeoPa]

That's certainly one of the important issues Beacon.

What I was suggesting (for posterity now as your way is fine for you) was to add extra fields in the data source (query) where the Beginner field would be set to 1 if the Skill Level was "beginner" and 0 otherwise. This would then be processed naturally within the report itself (as discussed previously).

Anyway, good luck with your project :)

[mlcampeau]

Ok...that makes sense, but whenever I try it, it asks me for a parameter value for the text box I reference in the Sum() expression.

Just to test out what you advised, I placed a text box in the details section, called txtBeginner, and typed in

 =IIF([SkillLevel] = "Beginner", 1, 0

and then I typed in

 =Sum([txtBeginner])

in a text box I placed in the Program footer.

When I go to Print Preview, it asks me for a parameter for txtBeginner. I also tried placing the text box in the Skill Level header, but the same thing happened.

Am I missing something else? It's like it doesn't recognize the txtBeginner text box, or it doesn't want to pass the value.

Have I made a mistake somewhere with this? Maybe I left out a step?

From my experience, you can't Sum an expression that is in a different section in the report. What I've done in the past to overcome this is create the =IIF([SkillLevel]="Beginner", 1, 0) textbox in the details section and name it txtBeginner. Then also in the details section, create another textbox that says =[txtBeginner] and go to the properties of the textbox and change it to Running Sum Over Group. Change Visible=No. Name it appropriately, like sumBeginner. Then in your footer, create a textbox =[sumBeginner].

[beacon]

From my experience, you can't Sum an expression that is in a different section in the report. What I've done in the past to overcome this is create the =IIF([SkillLevel]="Beginner", 1, 0) textbox in the details section and name it txtBeginner. Then also in the details section, create another textbox that says =[txtBeginner] and go to the properties of the textbox and change it to Running Sum Over Group. Change Visible=No. Name it appropriately, like sumBeginner. Then in your footer, create a textbox =[sumBeginner].

I tried that, but kept having the 'Enter Parameter' dialog box appear. For some reason it didn't like the fact that I created a text box in one section, as you suggested, and then try to reference that text box with the brackets around it in another section. Maybe I just completely missed something altogether.

[mlcampeau]

I tried that, but kept having the 'Enter Parameter' dialog box appear. For some reason it didn't like the fact that I created a text box in one section, as you suggested, and then try to reference that text box with the brackets around it in another section. Maybe I just completely missed something altogether.

From what I understood from your previous post was that you tried =sum([txtBeginner]) in your footer. Did you try the running sum invisible box in the details section and then just have a text box that says =[sumBeginner]? Because I know Access won't allow the aggregate functions on a calculated expression in a different section, but it has let me just reference a calculated textbox from a different section.