If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Report Record Source
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 |
#2
|
|||
|
|||
Report Record Source
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 "alex" wrote in message ... 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 |
#3
|
|||
|
|||
Report Record Source
On Jan 21, 12:18*pm, "Jeff Boyce" wrote:
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 "alex" wrote in message ... 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 |
#4
|
|||
|
|||
Report Record Source
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 On 21 Jan, 16:53, alex wrote: 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 |
Thread Tools | |
Display Modes | |
|
|