dlookup to count records on a report

[WatsonDyar]

I have a report with continuous class records. I want it to list the number of students current registered for that class.

The query lq_StudentCount is a totals query with the Class_ID (from the Class table) and Student_ID (from the Students table). The Class_ID is grouped, the Student_ID is counted. Both ID field contain numbers.

I have this in the text control field:

=Nz(DLookUp([StudentCount],"lq_MRTT_StudentCount","[ClassID]=" & [Reports]![r_MRTT_ClassSchedule]![Class_ID]),0)

I can't get this to work!:banghead:

 

[Galaxiom]

I can't get this to work!:banghead:

What result do you get when you try?

 

[WatsonDyar]

Well, first it is prompting me to enter parameters for StudentCount (which is what I have the count of Student_ID label as within the query)...the the #Error displays in the field.

Thank you!

 

[MarkK]

1) You cite a query called 'lq_StudentCount', but in your DLookUp() function call you are using a query called 'lq_MRTT_StudentCount.' Is this part of the problem?
2) What is going on with the first parameter in your DLookup()? That should be the name of the field you want to look up, but what is [StudentCount]? Is that a field in the report? Or do you mean "StudentCount" (in quotes), the name of a field?

=Nz(DLookUp("StudentCount","lq_MRTT_StudentCount","[ClassID]=" & Me.Class_ID),0)

 

[WatsonDyar]

1. No, I just left the MRTT off in the first post so no issue there.

2. StudentCount is the field from lq_MRTT_StudentCount that is counting the number of students (Student_ID).

NOW I've gotten it to stop prompting me for parameters but still getting the #Error on the report:

=Nz(DLookUp("StudentCount","lq_MRTT_StudentCount","[ClassID]=" & [Reports]![r_MRTT_ClassSchedule]![Class_ID]),0)

If I change the last criteria to Me.Class_ID as you suggested, it prompts for "Me" parameter value!

 

[MarkK]

Yeah, I always do this type of thing in VBA.
What if you do . . .

=Nz(DLookUp("StudentCount","lq_MRTT_StudentCount", "ClassID = " & [Class_ID]),0)

Is there a numeric field called 'ClassID' in the query 'lq_MRTT_StudentCount'?
Is there a control on the report called 'Class_ID'? Those are the things that need to exist for this DLookUp() to work.

 

[WatsonDyar]

Thank you Mark....This didn't work either. I think I may have a solution though. I'll post it soon.

 

[WatsonDyar]

Mark-

You're gonna want to strangle my neck: This entire time I was only missing an underscore:

=Nz(DLookUp("StudentCount","lq_MRTT_StudentCount", "Class _ID = " & [Class_ID]),0)

Thank you for the help- greatly appreciate it!!!

 

[Galaxiom]

This entire time I was only missing an underscore:

Yet another good reason to avoid using underscores in names.