Using [Enter date:] query criteria on report 2

[KerryL]

I've done this before so I know it's possible, but I'm having some brain-lock today and can't recall how I did it. X-)

I created a report (based on a query) which charts the total number of customer calls for each month. To do this, the RequestDate field is filtered in the query by the following criteria:

Between [Enter begin date:] And [Enter end date:]

This prompts the user to enter the date range. On the report itself, I have some text and a couple of text boxes that form this sentence:

For reporting period [begin date] through [end date].

My question is, how do I automatically populate the two text box date fields on the chart with the actual dates entered by the user?


I'll probably smack my forehead and say "DUH!" when someone reminds me how this is done, but so far my mind is blank. :-0 Thanks for your help,
Kerry

 

[AlexMidd]

Only way I can think of is to have two fields in the underlying query, one for begin date and one for end date. Set them up as enter fields like you have done already, but separately, i.e. [Enter Begin Date:] and [Enter End Date:]. Then, they should contain the values entered by the user.

Theoretically (I have not tried it so I cannot vouch for it) you can then refer to these fields in the query and use as the controlsource for controls, in calculated fields etc. So you could have another field in the query which uses the between operator. Have fun! )

Alex Middleton

 

[jocat]

In the "Control Source" property for the text box on the report, use the exact same name you used in the criteria field for the query. In your case, where you used "Between [Enter begin date:] And [Enter end date:]" you would enter in the control source of the text box, [Enter begin date:] for the beginning date and [Enter end date:] for the end date. --- be sure to include the colon - it must be exact!!

This should work.

Jocat

 

[KerryL]

Jocat,

I had tried that already and it didn't work, but the problem wasn't the Control Source of each text box. The problem was that I hadn't identified the query as the Record Source of the report. As soon as I linked the report to the correct query, the dates were inserted into the text boxes. (Duh!)

Thank you for shoving me in the right direction!

Kerry

 

[Kjonnnn]

Try

="For Period " & [Start Date] & " through " & [End Date]

My Report does the same thing and this works.

 

[KerryL]

Thank you Kjonnnn! I didn't realize I could put the [Start Date] and [End Date] right into the text. I had aligned separate text boxes to make it appear as a single sentence, but your solution makes it much easier.

Thank you!

...now if I could just get it to print a single page instead of creating a page for each record the query finds!