can anyone help me. i have created a access database, which includes dates and tick boxes. how can i create a report to count the tick boxes, that fall within a certain date that i specify.
Create a query to count the data, then create the report based on said query. Here's how:
Create the query to collect all of the information you need. Select only the tick boxes, and the date field.
Once you have selected all the relevant fields, click on "View-->Totals". A "Totals" row should now appear in the column header information. For the Date field, select 'Where' from the drop-down box. For the tick boxes, select 'Count'. Ensure that every field has a total type selected. In the Criteria row, type in 'True'.
Run the query, and make sure that it counts properly.
Now, for your 'date' field. Go to the Criteria row, and enter the following EXACTLY. Do NOT substitute a date between the [ and ], just type it in word for word.
Code:
>= [Enter a start date] AND <= [Enter an end date]
This will cause the query to prompt you for start and end dates when you run it. Try it out, and ensure it functions properly. You can modify the prompt text by changing what's between the [ and ].
Now, create your report, and base it on the query. When you run the report, it will prompt you for start and end dates.
For additional fun, you can have the start/end dates referenced from a field on a form. To do this, reference the fields in the query, eg.
Code:
>=[Forms]![Formname]![StartFieldName] AND <= [Forms]![Formname]![EndFieldName]
Create a button on the form to run the report.
Now, the user can enter the start and end dates on a form, then hit a button to run the report. When the user hits the button, the report references the query, which references the fields on the form.
The purist's way of doing this is to build a query to select the records you want.
For dates in a range, you want to use a syntax something like
... WHERE date-field-name BETWEEN #lower-date-limit# AND #upper-date-limit# ...
For only the checked boxes, you might want to use the fact that a tick box (actually, check box) is usually a Yes/No field. So include the tick box and in the WHERE clause add the qualifier to the above
... AND tick-box-name = True ...
Then build a report off the query using a wizard. The wizard will allow you to specify report breaks and other options. It will ALSO allow you to specify summary items such as sums or counts.
If you wish the date range to be variable, look up help topic "Parameter Query"
