DAvg in Access Report section

[Walt]

I've developed a custom report in design view (didn't use the wizard at all)
that shows summary statisitcs for an entire state and then by region. The
state's statistics are shown in the report header and each each region's
statisitcs are shown in the "Region" section that was created as a report
section.

Everything is working fine except for one small issue. One of the
statistics that is shown for both the total state and for each region is an
average of records where a particular criteria is met. I'm using the DAvg
function and it is working fine for the state rollup. My problenm is with
the regional rollups. I can't seem to get the syntax right within the
control so that the formula simply calculates the regional average for each
region when the criteria is met. I either get the same average that is
returned for the total state, or an "Error", or nothing at all depending on
how I've entered the criteria syntax. I even tried some VBA code like Me! or
Me!.Region within the formula as a desperate attempt. However, as a test,
when I key in the name of a particular region in the syntax, the formula
works fine, but it's meaningless because it will return the same exact
average for each region. Here is the control...

=DAvg("[Days]","qryIHBSServ","[ProviderType]='OUTSIDE' And [Region]='??????'")

The control's purpose is to return the average number of days for each
region from the domain qryIHBSServ (in this case, a query) where the Provider
Type = Outside. It's the ?????? part of the control that I've been pullling
my hair out over the past couple of days. I need to do the same thing for
another control where the Provider Type = Inside, but that shouldn't be any
problem at all once this is figured out. All I'd have to do is change the
syntax from Outside to Inside in the other formula.

As additional information, the control's syntax for the state's rollup is
exactly as shown above, except that it doesn't have the additonal criteria of
Region since it needs to calculate the average across the entire domain.
It's the addition of the Region criteria that's giving me a headache.

Any help would be greatly appreciated. Thanks!

 

[Duane Hookom]

Is the query qryIHBSServ also the report's record source? If so, you can use
the same control source in the Region and State sections:
=Sum(Abs([ProviderType]="OUTSIDE") *
[Days])/Sum(Abs([ProviderType]="OUTSIDE"))
This would be much more efficient and accurate than the DAvg() syntax.

Otherwise, try (assuming Region is text):
=DAvg("[Days]","qryIHBSServ","[ProviderType]='OUTSIDE' And [Region]='" &
[Region] & "'")

 

[Walt]

Thanks, Duane. =Sum... did the trick!

The second I saw the first few words in your response, I slapped my forehead
in stupidity! I should have thought of that especially since I had to use
the same concept elsewhere in the report. I guess after a couple of days of
frustration, I couldn't see the forest through the trees. I did try your
Plan B suggestion for the heck of it using your syntax for DAvg formula, but
"Error" was returned. Syntax for DAvg is convoluted and perplexing, but I'm
forced to use it at times when no other alternatives seem to present
themselves.

Thanks again!

Is the query qryIHBSServ also the report's record source? If so, you can use
the same control source in the Region and State sections:
=Sum(Abs([ProviderType]="OUTSIDE") *
[Days])/Sum(Abs([ProviderType]="OUTSIDE"))
This would be much more efficient and accurate than the DAvg() syntax.

Otherwise, try (assuming Region is text):
=DAvg("[Days]","qryIHBSServ","[ProviderType]='OUTSIDE' And [Region]='" &
[Region] & "'")

--
Duane Hookom
Microsoft Access MVP

I've developed a custom report in design view (didn't use the wizard at all)
that shows summary statisitcs for an entire state and then by region. The
state's statistics are shown in the report header and each each region's
statisitcs are shown in the "Region" section that was created as a report
section.

Everything is working fine except for one small issue. One of the
statistics that is shown for both the total state and for each region is an
average of records where a particular criteria is met. I'm using the DAvg
function and it is working fine for the state rollup. My problenm is with
the regional rollups. I can't seem to get the syntax right within the
control so that the formula simply calculates the regional average for each
region when the criteria is met. I either get the same average that is
returned for the total state, or an "Error", or nothing at all depending on
how I've entered the criteria syntax. I even tried some VBA code like Me! or
Me!.Region within the formula as a desperate attempt. However, as a test,
when I key in the name of a particular region in the syntax, the formula
works fine, but it's meaningless because it will return the same exact
average for each region. Here is the control...

=DAvg("[Days]","qryIHBSServ","[ProviderType]='OUTSIDE' And [Region]='??????'")

The control's purpose is to return the average number of days for each
region from the domain qryIHBSServ (in this case, a query) where the Provider
Type = Outside. It's the ?????? part of the control that I've been pullling
my hair out over the past couple of days. I need to do the same thing for
another control where the Provider Type = Inside, but that shouldn't be any
problem at all once this is figured out. All I'd have to do is change the
syntax from Outside to Inside in the other formula.

As additional information, the control's syntax for the state's rollup is
exactly as shown above, except that it doesn't have the additonal criteria of
Region since it needs to calculate the average across the entire domain.
It's the addition of the Region criteria that's giving me a headache.

Any help would be greatly appreciated. Thanks!