Access treats a Text Field name as a parameter

[Guest]

I have a nested report of the form:-

Region
Location
Report
<report date>
<other report dates>

I want to count the Locations, and how many locations in each region have
submitted at least one report.

To count locations I used the technique recomended in the Access help under
"Count the number of records in each group or report". This is to put an
invisible text field in the location header with a value of "=1".

I have called this field "LocCounter". I have no database or query fields of
this name.

In the Region header I then have a control whose Control Source is:-

="(" & Sum([LocCounter]) & " Locations)"

However when I run the report, Access insists on treating "LocCounter" as a
parameter. Is asks for this parameter before the report runs, then sums THAT
number for each Region!

I think i'm using exactly the technicque as documented in the help. Why does
Access think this is a parameter name, and how do I get at the report control
value as i want?

 

[Guest]

When you run the sum, you need to put the name of the field in the table and
not in the report.
So if you have a field in the report named LocCounter, and in the control
source of this field you have LocCounter_NameInTheTable, then the sum will be

=Sum([LocCounter_NameInTheTable])

 

[Guest]

Unfortunately this is proving to be very tricky. This is what i have found so
far:-

1) The SQL-style aggregate functions will only work on table values, not form
field values. (This is what Ofer was referring to).

2) The "use a form field as a counter" technique suggested in the Access help
won't work for me, because the counter value accumulates down the group,
meaning the count is only available at the bottom of the group in the
group
footer, but I need it in the group header.

3) Access MDB does not support Count(Distinct [fieldname]) which is at least
one of the things I want to calculate in the group header (grr...).

Basically this report worked when I only had one report date per location,
however once you have more than one report per location you start to
multiple-count the locations.

I really need Count Distinct to count the locations in a region, then
functions to (for example) count how many Locations have submitted at least
one report (i.e. where the report date is not null).

I'm beginning to think that maybe the Access report writer alone can't
actually do this, and that I need extra columns in the source query to
pre-calculate some of the counts. However the lack of Count Distinct makes
even that more difficult than one would expect. I may have to give up
temporarily an pre-aggregate the bottom level of the report in the query so I
have only one row per location.

When you run the sum, you need to put the name of the field in the table and
not in the report.
So if you have a field in the report named LocCounter, and in the control
source of this field you have LocCounter_NameInTheTable, then the sum will be

=Sum([LocCounter_NameInTheTable])

--
\\// Live Long and Prosper \\//
BS"D

I have a nested report of the form:-

Region
Location
Report
<report date>
<other report dates>

I want to count the Locations, and how many locations in each region have
submitted at least one report.

To count locations I used the technique recomended in the Access help under
"Count the number of records in each group or report". This is to put an
invisible text field in the location header with a value of "=1".

I have called this field "LocCounter". I have no database or query fields of
this name.

In the Region header I then have a control whose Control Source is:-

="(" & Sum([LocCounter]) & " Locations)"

However when I run the report, Access insists on treating "LocCounter" as a
parameter. Is asks for this parameter before the report runs, then sums THAT
number for each Region!

I think i'm using exactly the technicque as documented in the help. Why does
Access think this is a parameter name, and how do I get at the report control
value as i want?

 

[Guest]

For simple reports, you can just add another Group By level.

Then you can count the groups instead of counting the records.

For complex reports, you can use DCount to get counts of particular values.

For very complex reports, you can write a series of VBA functions
which just increment a counter, base on complex criteria.

For very very complex reports, you can use subreports to do all
the calculations without displaying the data, and copy the data
off the subreport onto the main report for display.

If the queries become to complex, bind the report to a temp table,
and in the open event do sequential operations to fill the table fields
as required, including any subtotals required.

(david)

Unfortunately this is proving to be very tricky. This is what i have found so
far:-

1) The SQL-style aggregate functions will only work on table values, not form
field values. (This is what Ofer was referring to).

2) The "use a form field as a counter" technique suggested in the Access help
won't work for me, because the counter value accumulates down the group,
meaning the count is only available at the bottom of the group in the
group
footer, but I need it in the group header.

3) Access MDB does not support Count(Distinct [fieldname]) which is at least
one of the things I want to calculate in the group header (grr...).

Basically this report worked when I only had one report date per location,
however once you have more than one report per location you start to
multiple-count the locations.

I really need Count Distinct to count the locations in a region, then
functions to (for example) count how many Locations have submitted at least
one report (i.e. where the report date is not null).

I'm beginning to think that maybe the Access report writer alone can't
actually do this, and that I need extra columns in the source query to
pre-calculate some of the counts. However the lack of Count Distinct makes
even that more difficult than one would expect. I may have to give up
temporarily an pre-aggregate the bottom level of the report in the query so I
have only one row per location.

When you run the sum, you need to put the name of the field in the table and
not in the report.
So if you have a field in the report named LocCounter, and in the control
source of this field you have LocCounter_NameInTheTable, then the sum will be

=Sum([LocCounter_NameInTheTable])

--
\\// Live Long and Prosper \\//
BS"D

I have a nested report of the form:-

Region
Location
Report
<report date>
<other report dates>

I want to count the Locations, and how many locations in each region have
submitted at least one report.

To count locations I used the technique recomended in the Access help under
"Count the number of records in each group or report". This is to put an
invisible text field in the location header with a value of "=1".

I have called this field "LocCounter". I have no database or query fields of
this name.

In the Region header I then have a control whose Control Source is:-

="(" & Sum([LocCounter]) & " Locations)"

However when I run the report, Access insists on treating "LocCounter" as a
parameter. Is asks for this parameter before the report runs, then sums THAT
number for each Region!

I think i'm using exactly the technicque as documented in the help. Why does
Access think this is a parameter name, and how do I get at the report control
value as i want?

 

[Guest]

Thanks for the tips David, much appreciated. Comments below.

For simple reports, you can just add another Group By level.

Then you can count the groups instead of counting the records.

Interesting. It looks (surpisingly) as if Access will let me group on
Location multiple times. Maybe this would let me do what you propose without
having to introduce extra dummy columns into the query for grouping purposes.

For complex reports, you can use DCount to get counts of particular values.

I tried this one. However I hit a wall when I tried to do the equivalent of
a count distinct by using DCount with a Where clause (3rd parameter)
something like:

"[Region]= '" & [Region] @ "' & [ReportDate]= Max([ReportDate])"

(The above syntax may be wrong, i'm typing on the fly). Althoug I tested the
where clause standalone and it seemed OK, it fell over with a syntax error
when used as the DCount paramater. I ran out of time to hunt this down and
ended up pre-aggregating the query down to one line per Location just to get
something running.

For very complex reports, you can write a series of VBA functions
which just increment a counter, base on complex criteria.

Need to practice that one.

For very very complex reports, you can use subreports to do all
the calculations without displaying the data, and copy the data
off the subreport onto the main report for display.

As above.

If the queries become to complex, bind the report to a temp table,
and in the open event do sequential operations to fill the table fields
as required, including any subtotals required.

You've clearly been there and done that ;-)

Thanks for all the ideas Daid.

(david)

Unfortunately this is proving to be very tricky. This is what i have found so
far:-

1) The SQL-style aggregate functions will only work on table values, not form
field values. (This is what Ofer was referring to).

2) The "use a form field as a counter" technique suggested in the Access help
won't work for me, because the counter value accumulates down the group,
meaning the count is only available at the bottom of the group in the
group
footer, but I need it in the group header.

3) Access MDB does not support Count(Distinct [fieldname]) which is at least
one of the things I want to calculate in the group header (grr...).

Basically this report worked when I only had one report date per location,
however once you have more than one report per location you start to
multiple-count the locations.

I really need Count Distinct to count the locations in a region, then
functions to (for example) count how many Locations have submitted at least
one report (i.e. where the report date is not null).

I'm beginning to think that maybe the Access report writer alone can't
actually do this, and that I need extra columns in the source query to
pre-calculate some of the counts. However the lack of Count Distinct makes
even that more difficult than one would expect. I may have to give up
temporarily an pre-aggregate the bottom level of the report in the query so I
have only one row per location.

When you run the sum, you need to put the name of the field in the table and
not in the report.
So if you have a field in the report named LocCounter, and in the control
source of this field you have LocCounter_NameInTheTable, then the sum will be

=Sum([LocCounter_NameInTheTable])

--
\\// Live Long and Prosper \\//
BS"D


:

I have a nested report of the form:-

Region
Location
Report
<report date>
<other report dates>

I want to count the Locations, and how many locations in each region have
submitted at least one report.

To count locations I used the technique recomended in the Access help under
"Count the number of records in each group or report". This is to put an
invisible text field in the location header with a value of "=1".

I have called this field "LocCounter". I have no database or query fields of
this name.

In the Region header I then have a control whose Control Source is:-

="(" & Sum([LocCounter]) & " Locations)"

However when I run the report, Access insists on treating "LocCounter" as a
parameter. Is asks for this parameter before the report runs, then sums THAT
number for each Region!

I think i'm using exactly the technicque as documented in the help. Why does
Access think this is a parameter name, and how do I get at the report control
value as i want?

 

[Guest]

"[Region]= '" & [Region] @ "' & [ReportDate]= Max([ReportDate])"

Simple problems made simple, but complex problems remain
complex :~(.

You can use Dcount to add complex sorting and grouping,
but you need complex queries underneath - it doesn't simplify
the problem at all.

Normally, you can't use an aggregate function in a criteria
unless the same value appears in the field list.

You've clearly been there and done that ;-)

.... wasted youth ..

(david)

Thanks for the tips David, much appreciated. Comments below.

For simple reports, you can just add another Group By level.

Then you can count the groups instead of counting the records.

Interesting. It looks (surpisingly) as if Access will let me group on
Location multiple times. Maybe this would let me do what you propose without
having to introduce extra dummy columns into the query for grouping purposes.

For complex reports, you can use DCount to get counts of particular

values.

I tried this one. However I hit a wall when I tried to do the equivalent of
a count distinct by using DCount with a Where clause (3rd parameter)
something like:

"[Region]= '" & [Region] @ "' & [ReportDate]= Max([ReportDate])"

(The above syntax may be wrong, i'm typing on the fly). Althoug I tested the
where clause standalone and it seemed OK, it fell over with a syntax error
when used as the DCount paramater. I ran out of time to hunt this down and
ended up pre-aggregating the query down to one line per Location just to get
something running.

For very complex reports, you can write a series of VBA functions
which just increment a counter, base on complex criteria.

Need to practice that one.

For very very complex reports, you can use subreports to do all
the calculations without displaying the data, and copy the data
off the subreport onto the main report for display.

As above.

If the queries become to complex, bind the report to a temp table,
and in the open event do sequential operations to fill the table fields
as required, including any subtotals required.

You've clearly been there and done that ;-)

Thanks for all the ideas Daid.

(david)

Unfortunately this is proving to be very tricky. This is what i have

found
so

far:-

1) The SQL-style aggregate functions will only work on table values,

not
form

field values. (This is what Ofer was referring to).

2) The "use a form field as a counter" technique suggested in the

Access
help

won't work for me, because the counter value accumulates down the group,
meaning the count is only available at the bottom of the group in the
group
footer, but I need it in the group header.

3) Access MDB does not support Count(Distinct [fieldname]) which is at least
one of the things I want to calculate in the group header (grr...).

Basically this report worked when I only had one report date per location,
however once you have more than one report per location you start to
multiple-count the locations.

I really need Count Distinct to count the locations in a region, then
functions to (for example) count how many Locations have submitted at least
one report (i.e. where the report date is not null).

I'm beginning to think that maybe the Access report writer alone can't
actually do this, and that I need extra columns in the source query to
pre-calculate some of the counts. However the lack of Count Distinct makes
even that more difficult than one would expect. I may have to give up
temporarily an pre-aggregate the bottom level of the report in the

query
so I

have only one row per location.

:

When you run the sum, you need to put the name of the field in the

table
and

not in the report.
So if you have a field in the report named LocCounter, and in the control
source of this field you have LocCounter_NameInTheTable, then the

sum
will be

=Sum([LocCounter_NameInTheTable])

--
\\// Live Long and Prosper \\//
BS"D


:

I have a nested report of the form:-

Region
Location
Report
<report date>
<other report dates>

I want to count the Locations, and how many locations in each

region
have

submitted at least one report.

To count locations I used the technique recomended in the Access

help
under

"Count the number of records in each group or report". This is to

put
an

invisible text field in the location header with a value of "=1".

I have called this field "LocCounter". I have no database or query fields of
this name.

In the Region header I then have a control whose Control Source is:-

="(" & Sum([LocCounter]) & " Locations)"

However when I run the report, Access insists on treating

"LocCounter"
as a

parameter. Is asks for this parameter before the report runs, then sums THAT
number for each Region!

I think i'm using exactly the technicque as documented in the

help.
Why does

Access think this is a parameter name, and how do I get at the

report
control

value as i want?