Report Record Source

[alex]

Hello,

I’m starting to build about five reports and I’m trying to do my
homework before I start…

My main question/concern is the report’s record source; i.e., what to
base the report on.

I’ve always thought to base most everything (forms or reports) off of
queries (they can add functionality and protect the data). I looked
at some of the example databases, such as the Time and Billing mdb,
but many of those reports use a SQL statement that pulls data directly
from a table.

My reports are in a TimeAccounting database and need to be dynamic and
fluid. The user needs to be able to select (e.g.,) one employee or
all employees, one day or many days. I thought about creating a form
with list or combo boxes and then using the selections in the criteria
section (as an expression) of a query or multiple queries. I’ve read
in this group, however, to avoid using form parameters in a query and
instead take the values from the form and build my own where clause.
The reasoning makes sense, but I’m not sure how to do it! Where does
the [where] go?

How exactly do the values from a form make their way to the report (or
to the query and then to the report)?

I realize the question is a little abstract, but any insight on this
would be helpful. I’m especially interested in basing a report on a
form/query without a parameter in the query, i.e., using the Where
clause.

alex

 

[Jeff Boyce]

The values from the form do NOT make their way into the report.

I think you'll find considerable support for using queries to feed your
reports.

In your queries, you can (and probably already do) use the values entered on
a form as selection criteria in the queries. The selection criterion will
look something like:

Forms!YourFormName!YourControlName

Note that this approach requires the form to be open for the queries to work
(i.e., for the reports to work).

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hello,

I’m starting to build about five reports and I’m trying to do my
homework before I start…

My main question/concern is the report’s record source; i.e., what to
base the report on.

I’ve always thought to base most everything (forms or reports) off of
queries (they can add functionality and protect the data). I looked
at some of the example databases, such as the Time and Billing mdb,
but many of those reports use a SQL statement that pulls data directly
from a table.

My reports are in a TimeAccounting database and need to be dynamic and
fluid. The user needs to be able to select (e.g.,) one employee or
all employees, one day or many days. I thought about creating a form
with list or combo boxes and then using the selections in the criteria
section (as an expression) of a query or multiple queries. I’ve read
in this group, however, to avoid using form parameters in a query and
instead take the values from the form and build my own where clause.
The reasoning makes sense, but I’m not sure how to do it! Where does
the [where] go?

How exactly do the values from a form make their way to the report (or
to the query and then to the report)?

I realize the question is a little abstract, but any insight on this
would be helpful. I’m especially interested in basing a report on a
form/query without a parameter in the query, i.e., using the Where
clause.

alex

 

[alex]

The values from the form do NOT make their way into the report.

I think you'll find considerable support for using queries to feed your
reports.

In your queries, you can (and probably already do) use the values enteredon
a form as selection criteria in the queries.  The selection criterion will
look something like:

    Forms!YourFormName!YourControlName

Note that this approach requires the form to be open for the queries to work
(i.e., for the reports to work).

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hello,

I’m starting to build about five reports and I’m trying to do my
homework before I start…

My main question/concern is the report’s record source; i.e., what to
base the report on.

I’ve always thought to base most everything (forms or reports) off of
queries (they can add functionality and protect the data).  I looked
at some of the example databases, such as the Time and Billing mdb,
but many of those reports use a SQL statement that pulls data directly
from a table.

My reports are in a TimeAccounting database and need to be dynamic and
fluid.  The user needs to be able to select (e.g.,) one employee or
all employees, one day or many days.  I thought about creating a form
with list or combo boxes and then using the selections in the criteria
section (as an expression) of a query or multiple queries.  I’ve read
in this group, however, to avoid using form parameters in a query and
instead take the values from the form and build my own where clause.
The reasoning makes sense, but I’m not sure how to do it!  Where does
the [where] go?

How exactly do the values from a form make their way to the report (or
to the query and then to the report)?

I realize the question is a little abstract, but any insight on this
would be helpful.  I’m especially interested in basing a report on a
form/query without a parameter in the query, i.e., using the Where
clause.

alex

As always Jeff, thanks for the comments.
I do use the value in my forms as the selection criteria.
alex

 

[troy23]

Yes queries are your best bet. They are compiled and are the faster
way of pulling back data.

Base your report on a query

For FREE Access ebook and videos click here
http://access-databases.com/ebook