Report based on selections on a form

[Guest]

I have a form that has about 80 items each one has an option group for yes,
no, and N/A. This updates a table for each itme for each person. Now I have
a form for the user to create a report, but gives the user options to narrow
the criteria for the report, like date, section, etc.

I could create a report for every possiblity the user can select, but I
would rather have one report and build in a statement to populate the report.

The report shows these 80 items in a column and 3 boxes next to each item to
display the count of yes, no, and N/A. I am trying to get an overall count
for every record base on the criteria the user selected to create the report.
I can't use the recordsource because it gives me a report for each record.
I tried using a crosstab query, but that didn't seem to help. So for each of
these boxes I created a formula to count each item. However, since I want to
use one report to display the information based on the user selection from
the form I am stuck on how to do this. I thought about creating a temp table
and delete the items before previewing the report then populate it with all
records with the possibility of narrow it down by the user selection.

I hope I am making sense here.

How can I excute a query "append query" based on a strSQL string? This will
populate my temp table. I can then base the formula for each box on each
itme in this table.

Any help or other ideas on how to do this would be appreciated,

 

[David Lloyd]

I am not sure I understand all the details of your report, but one
suggestion I would make would be to look at using the DCount function. This
function allows you to count the number of items in a table or query
separate from the report Record Source. The DCount function also allows you
to specify criteria to limit the records included in the count.

If the user inputs from the form are not shown visibly on the form, then you
can consider using hidden textboxes to reference there values on the report
and in the DCount function.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have a form that has about 80 items each one has an option group for yes,
no, and N/A. This updates a table for each itme for each person. Now I
have
a form for the user to create a report, but gives the user options to narrow
the criteria for the report, like date, section, etc.

I could create a report for every possiblity the user can select, but I
would rather have one report and build in a statement to populate the
report.

The report shows these 80 items in a column and 3 boxes next to each item to
display the count of yes, no, and N/A. I am trying to get an overall count
for every record base on the criteria the user selected to create the
report.
I can't use the recordsource because it gives me a report for each record.
I tried using a crosstab query, but that didn't seem to help. So for each
of
these boxes I created a formula to count each item. However, since I want
to
use one report to display the information based on the user selection from
the form I am stuck on how to do this. I thought about creating a temp
table
and delete the items before previewing the report then populate it with all
records with the possibility of narrow it down by the user selection.

I hope I am making sense here.

How can I excute a query "append query" based on a strSQL string? This will
populate my temp table. I can then base the formula for each box on each
itme in this table.

Any help or other ideas on how to do this would be appreciated,