Need detail values to display in footer

swilliams2785

Registered User.
Local time
Today, 10:07
Joined
Jun 24, 2010
Messages
19
I’m trying to capture values of repeating numeric and text fields in the report detail and display them in a footer. The attached database is a better explanation of what I’m trying to do.
 

Attachments

There are a couple of ways. I've shown you two, i.e. saving the values into an array and reading it off at the footer or using a DLookup() function. See attached.
 

Attachments

The DLookup function didn't work with repeating sets of records but the array will do the job. This is my first foray into VAB within reports. I'm sure I'll use this technique many times in the future. Thank you.
 
An additional problem has surfaced with my report. Although the fix suggested solved the original problem, in my application I need to have the detail and GP footer invisible. Is there a way to make this work with these conditions in place? See attached file.
 

Attachments

In that case use the DLookup function and set the third argument to 1, 2, 3 or 4 depending on the case, change the first argument to. Here's how to use the DLookup() function:

http://www.techonthenet.com/access/functions/domain/dlookup.php

This can be done even more smoothly in the Detail section instead, sort by GP in the report and concatenate the GP number to the text, so the textbox will have a control source like:

="Value of NS when GP=" & [GP]
 
Success. I had to fugure out how to use multiple criteria (the link you suggested really helped) and discovered that both fields referenced in the criteria had to be present in the footer where the DLookUp function was being used. Thanks again, hopefully for the last time. I guess I must conclude that arrays can't be built or referenced from invisible report sections.
 
It's just hidden but it should still render/format. Was the array not working?
 
The array worked great until I changed the detail section's visible property to No. After that no values were displayed. I just checked the file you sent me and it does the same thing.
 
The only reason is because it's in the Print event. Move it to the Format event and it will still work.
 
Thanks for the suggestion, I'll give it a try. I think this may help me solve another problem I am having trying to load a function into the Control Source property of a text box on my report. I get an error message saying that you can not load the Control Source property during a print operation. I'll let you know how it goes.
 
If your function name is GetMyValue then in the control source property you would put:

=GetMyValue()

The function also must be in a Module and must be declared as Public.

See how it goes.
 
I am pleased to report successful resolutions to all issues encountered to date. Your help was the key and is greatly appreciated. Thank you.
 

Users who are viewing this thread

Back
Top Bottom