Inputting a date and producing a report from it.

OK, so this one's a little complex.

Imagine. You have a query which requires you to enter something into a parameter - like a date or something. That query is then used to generate a report. But you'd like the search criteria you entered into the box to go on the report.

So, say there's a list of appointments for a particular day for a dentist or whatever. It would make sense to put the date the appointments are for on the report.

Step 1 - First create a form:

The entire purpose of this form is to enter a date (or two dates, or whatever you're searching for).

• You need a textbox (and it must be a textbox!) on the form to type the date into.
• Right click on the textbox and go into the properties. Change the name of the textbox to txtDate (or something similar).
  

• Save the form as something like frmGetDate

Step 2 - Next you need a query:

Say to list the appointments someone has on that a day.

Like this:
BUT – instead of using a standard parameter criteria in the date column ([Enter the date] say) you type:

[forms]![frmGetDate]![txtDate]

What this does is put the date you enter in txtDate on frmGetDate into the parameter search. Clever eh? Like this:

But, if you run the query it won’t play. There are good reasons for this.

Step 3 - Create a report based on the query:

Just use a wizard for now, you can go back and work on this later.

Step 4 - Now the clever bit:

What you have to do is run the report from the form.

So go back to the form and put a Command Button in it which runs the query – you do this under Report Operations.

This should work – but it won’t show the date you typed into the textbox on the report. Yet.

Step 5 - The other clever bit:

What you need to do is go back to the report in Design view.

Create a textbox on the report somewhere – probably best to put it in the header of the report by the way

Right click on it and in the Data tab you need to type:

=Forms!frmGetDate!txtDate

into the control source box. Like this:

Step 6 - Now, try it out:

Open the form, hit the button and see what goes on!!This is a bit tricky of course, but you should be able to get there.

There’s no reason why you can’t enter two dates in the form – just give them different names (like txtDateStart and txtDateFinish). You could then stick two dates into the query using these names and then the report. Logically.

There’s also no reason why you couldn’t use a calendar control or spinners to populate the textboxes on the form for that matter – you could also set default values and/or input masks on the form (right click and properties) of course.

Total amounts of well coolness.