I have a report with two groups identified, STDNO followed by GP. The value of GP in the table is either 1, 2, 3, or 4 and follows this sequence all the way through. I have a text box in the GP footer that captures the value of a field named NS in the detail section of the report based on the value of GP. The text box Control Source contains =IIf([GP]=1,[NS]). After I confirmed that the formula was working I copied it to a new text box and changed the formula to test for a value of 2 for GP. The formulas in the first box works perfectly but the second one always returns a blank. I have placed the GP field in the footer to test the value and it displays as expected. I even tried changing the test value in the first box to 2 and then it returns blanks. The same thing happens for GP values of 3 and 4. Any ideas?

things in the footer or header will only show one value not the value for each record. I'm still a bit fuxxy on what it is you are trying to get. Can you post a screenshot here of what you have and are trying to do? Please upload the pics to here and not use a public photo sharing site because those are blocked by my work.

And remember that if there's more than one record displayed in the Detail section then the comparison you're doing will compare the LAST record. Also your IIF() function is incomplete and shouldn't work. You must supply 3 arguments.

=IIf([GP]=1,[NS], 3rd argument)

I was preparing a scaled down version of my data to attach for your perusal and found to my surprise that it worked, that is until I saved it. Now I have a new problem. I get a #Name error in the GP footer. When the values did display the report works as I had intended it to.

To recap, I want the value of NS for every value of GP to display in the STDNO footer. The attached database will give you an idea of what I’m trying to do so any suggestions would be appreciated.

Steve

Is the attached what you're after?

Also, I'm hoping that your table isn't how it is in your original db?

vbaInet,

Yes, this is what I'm looking for and I can tweak my layout and get it but once the database is saved then reopened the #ERROR? appears.

Right now the table is what I'm using. It is original data pulled from a spreadsheet that will be supplied to me. I was testing layout options for a final report and this was the quick and dirty way to get there fast. This is data for a first grade report card. The number fields are the major categories for the report and most of those have two or more subcategories for more detailed information. (See attached image) They are checked if the student is not meeting the expected target level. Any thoughts on further normalization would be welcome as well.

I didn't have to do anything, it just worked as supposed to. What do I have to do to replicate the problem?

It happens to me after I save the database then close and reopen Access. The attached .jpg is what I see.

This is strange! Out of frustration I saved the database, created in Access 2007, in Access 2002 format and put it into a laptop I have running the 2002 version. The problem disappears and data displays as expected.

The latest twist . . . . I put the database on yet another laptop running 2007 with Windows 7 and had no problems. I guess my problem is with my pc and my installation of Access. If you can think of any explanation for this scenario I'm all ears otherwise thanks for your time and effort. Your assistance is greatly appreciated.

How about the attached?

Here's a link (http://r937.com/relational.html) on normalization. Your certainly need to normalize. That may be the problem too.

How about the attached?

Here's a link (http://r937.com/relational.html) on normalization. Your certainly need to normalize. That may be the problem too.

This seems to work. I can't figure out why but I'm not going to lose any more sleep over it. Thank you for the solution and the link.

All I did was base your report on a query instead of using the table directly. Always good practice for forms and reports.

Cheerios!