Domain Aggregate Function Problem

[bhujanga]

I have made a report that lists permits issued, which is broken down by county. It works fine. At the top of the report (in the Report Header section), I use a domain aggregate function to get a total of all the permits in the time range. To be prcise, the formula is:

="A Total of " & DCount([Region],"General Permits","[IssueDate] >= [BeginDate] and [IssueDate] <= [EndDate]") & " Permits were Issued During this Period"

Note[Region] just happens to be a field that is never null)

This works fine too.

Here's the thing. I'm doing a summary only version of the report, that just shows the total number of permits by county, but doesn't list them. Hence, I have eliminated the Detail section of the report. All other things stay the same. Suddenly the domain aggregate function returns zero. I can put the Detail section back in even with no objects in it, and voila, the function again returns a value. Since there are lots of permits, even using the tiniest allowable height for the Detail section causes unacceptable distortion to the layout.
Why would such a thing happen and how can I work around it?

 

[PWise]

try setting the detail
Visible= false

 

[dhookom]

I'm not sure why you are using DCount(). If you want to display the number of records in your report, it would be more efficient and accurate to use:

="A Total of " & Count(*) & " Permits were Issued During this Period"

As pwise suggests, you can set the detail section to invisible if you don't want to see all the details.


Duane
Hook'D on Access
MS Access MVP

 

[bhujanga]

As you suggest, making the detail section invisible solves the problem, although I find it puzzling that the detail section has to have a size for a domain aggregate function to work, since it has nothing to do with the data. I'd be interested to know the reason if you happen to know, otherwise I will learn to live with the mystery.

Incidentally, I tried the Count(*) function and it comes up as #error unless I put it in the Report footer and I wanted them to see this information at the front. Is there a way to make it work in the header?

Thanks for both of your help.

 

[dhookom]

I expect your "Header" is probably the Page Header. Aggregate functions such as Sum(), Count(), Avg(), etc won't work in Page Sections. They should all work in Report and Group sections. Try move the text box to the Report Header section.

I believe reports are optimized. Typically the SQL that is the report's record source gets re-written based on it Filter and Sorting/Grouping. It is probably smart enough to notice if there is no detail section. If this is the case, it might convert the record source to a totals query. (this is just a guess).

Duane
Hook'D on Access
MS Access MVP

 

[bhujanga]

You were correct it was a page header.
Thanks.

 

[dhookom]

Common mistake and easy fix ;-)

Duane
Hook'D on Access
MS Access MVP