Trouble with Conditional Function in Report Page Footer

[mommasgonna]

---

I am struggling with building and If Then statement in my Report Footer. I have a report that pulls data from two tables...Students and Homeroom. This gives me a list of the students in each homeroom class and they are separated by homeroom. I would like to use a function in the page footer that counts the number of students who are members of our PTSA organization and then returns a percentage based on the total number of students in each homeroom class.



In the Report, I have on each page:
Page Header: Homeroom, LastName, FirstName, FamilyID
Details: LastName, FirstName, FamilyID

What I need is for the function to look at the FamilyID field and determine is there is any value entered. Then I need a count the total number of records in that homeroom and the total number of records with a value, so I can then get a percent of how many students actually belong to the PTSA in each homeroom.

I have tried and failed and am

Thanks for any help anyone can offer!!!

[Dal Jeanis]

1) IIF(NZ([FamilyID],0)>0,1,0) will get you a 1 for each student with FamilyID set, 0 otherwise.
SUM of that for the homeroom will give you the number of students with FamilyID set.
COUNT of that for the homeroom will give you the total number of students in the homeroom.
You might also use DSUM and DCOUNT respectively, depending on where you are putting the calculation.


2) I don't know why the fields other than Homeroom are on the Page Header, since those are detail fields in this example.

[mommasgonna]

This is what I have (.jpg image attached). The results I am getting are not what I expect, though. When I open the report in print preview mode, I am prompted for input on the field that contains the IIF function. The SUM expression is not adding up the total value for the field, it seems to be counting the number of students, not summing.

http://i1166.photobucket.com/albums/...portdesign.jpg

I am quite rusty with all of this. I took the last 14 years off...lol. I used to be a Microsoft Office User Specialist back on the 97 and 2000 versions of all of the office products. I feel like a dunce right now!

[ItsMe]

If you are still having trouble with this, I may be able to take a look at on Saturday. You can delete all of the private data and upload a sample in a compressed file.

[mommasgonna]

I think I got it! I got to sit down yesterday and really focus and I have all of the calculations in place. Just need to get the format done. Thanks again for the help! The suggestions from Dal Jeanis helped clear out the cobwebs! I appreciate all the willingness to help! I also got some great tips from another forum...not feeling quite as inadequate anymore! LOL

[ItsMe]

Post #2 sure looked like solid info to me. Glad you were able to make it work! If you feel the problem has been resolved, go ahead and mark this thread "Solved".

[mommasgonna]

I have no idea how to mark it solved...I am new to this board.

[ItsMe]

At the top of this thread, under the thread title and to the right, there are thread tools. Use the "Thread Tools" menu to select the "Mark this thread as solved" option.