Query/report with user-defined criteria

[sfjnet]

Hi
I am relatively new to Access so I hope I am not embarrassing myself with this question.

I have created a database showing contract details (contract number, contractors, dates, other relavant info). Also on the data entry form there is a separate field for each month of the financial year and the user can enter a value in the relevant month(s) corresponding to the number of hours the contractor has worked that month.

The users will have the option to run reports showing detail from the database but will not define their own queries. Of course I can set up generic queries/reports showing info such as hours worked for each month across the whole financial year, however I would like the user to be able to customise the query/report in a simple way so that they can see specific info, such as only the contract number / contractor / months they are interested in. In other words they should be able to run a report which allows them to choose (from drop down lists?) a specific contract number and/or contractor and/or month(s) of the year, and which shows only those choices on the report. The users won't want to (and won't know how to) edit the queries so I am hoping it is possible to put a "front end" on a report that will ask them to specify which info they require.
I know Access is very powerful so I imagine this is possible somehow but I don't know how to start.

Many thanks

[ADezii]

Hi
I am relatively new to Access so I hope I am not embarrassing myself with this question.

I have created a database showing contract details (contract number, contractors, dates, other relavant info). Also on the data entry form there is a separate field for each month of the financial year and the user can enter a value in the relevant month(s) corresponding to the number of hours the contractor has worked that month.

The users will have the option to run reports showing detail from the database but will not define their own queries. Of course I can set up generic queries/reports showing info such as hours worked for each month across the whole financial year, however I would like the user to be able to customise the query/report in a simple way so that they can see specific info, such as only the contract number / contractor / months they are interested in. In other words they should be able to run a report which allows them to choose (from drop down lists?) a specific contract number and/or contractor and/or month(s) of the year, and which shows only those choices on the report. The users won't want to (and won't know how to) edit the queries so I am hoping it is possible to put a "front end" on a report that will ask them to specify which info they require.
I know Access is very powerful so I imagine this is possible somehow but I don't know how to start.

Many thanks

This "Front End" would exist on a Form and would allow the User to selectively choose Fields that would be contained in the Report. An SQL string is dynamically built from the selections made, and would be the underlying Record Source for the Report. A Command Button on the Form would open the Report based on the selected criteria. Depending on the choices made, Control Sources, Labels, Report Header/Footer information, Grouping Levels, etc. would have to dynamically be created for the Report. It can be done but is not a walk-in-the-park. The other Option, as you have previously stated, is to create Generic Queries/Reports.

[brynne]

Apologies to the more advanced developers in this forum. I don't do much VBA programming so most of the functionality in my databases is based on simple queries and macros.

I would create a new form with one or more unbound text boxes into which the user would enter the desired criteria, e.g. month, contract. (This must match the data type for the field in the table you are running the query on, so I often use a drop down list to prevent typos.) Go into the properties for the text box(es) and give it/them a meaningful name. Then create a query as the basis for your report. As the criteria for the field(s) that the report will be based around, you use the name of the text box(es) you have just created in the form (I use the expression builder). Then you can add a button to run the report you created from that query.

Simple, but it works.

B

[NeoPa]

You really don't need to apologise for submitting an idea. If it's the worst idea ever, it's still an addition and can be looked at, and sometimes learned from, by others (experts as well). I've picked up a lot of ideas from posters adding to threads I've contributed to.
If it's not a great idea, then the OP can always choose another one if there, otherwise, good or not, it's the best one anyway.