Access treats a Text Field name as a parameter

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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])
 
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.

Ofer said:
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


colin_e said:
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?
Click to expand...
Click to expand...
 
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)

colin_e said:
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.

Ofer said:
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


colin_e said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the tips David, much appreciated. Comments below.

david@epsomdotcomdotau said:
For simple reports, you can just add another Group By level.

Then you can count the groups instead of counting the records.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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

Thanks for all the ideas Daid.
(david)

colin_e said:
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.

Ofer said:
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?
Click to expand...
Click to expand...
Click to expand...
 
"[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 ;-)
Click to expand...

.... wasted youth ..

(david)

colin_e said:
Thanks for the tips David, much appreciated. Comments below.

david@epsomdotcomdotau said:
For simple reports, you can just add another Group By level.

Then you can count the groups instead of counting the records.
Click to expand...

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
Click to expand...
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.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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

Thanks for all the ideas Daid.
(david)

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

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

2) The "use a form field as a counter" technique suggested in the
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
region
have
submitted at least one report.

To count locations I used the technique recomended in the Access
Click to expand...
help
under
"Count the number of records in each group or report". This is to
Click to expand...
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
Click to expand...
"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
Click to expand...
help.
Why does
Access think this is a parameter name, and how do I get at the
Click to expand...
report
control
value as i want?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top