Passing Query Requested Values 2

[pbt1234]

I have a report that's data source is a query. This query selects records based on a beginning date and ending date provided by the user. The user is prompted by the query (I used >=[Enter Begin Date] AND<= [Enter End Date] in the criteria field of design view) to prompt the user to supply this data. When prompted the user has to enter the beginning date on the standard Access msgBox and the ending date as well. The data is then collected and charted in my report. I want to capture the beginning date and ending date that the user enters and then add this information to the report. How can I do this?

Thanks in advance!
pbt1234

 

[scriverb]

Just refer to the Beginning date value and the Ending date value just as the SQL prompted for them:

=[Enter Begin Date]

=[Enter End Date]

Put the above code in a textbox's Control source and the dates entered will now display in your report.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[pbt1234]

I tried doing this (i.e. I created a text box in my report and called it txtDate and gave it the control source of "=[Enter Begin Date]" and recieved the standard ?Name error when the report opened. I tried to do the same using "=[Enter End Date] and same result. What am I doing wrong?

Thanks for your help!
pbt1234

 

[scriverb]

Leave the quotes and equals sign off. Treat the prompt just a s a field name.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[pbt1234]

I think that the problem may be that my report is a chart. The chart has the data source linked to it and not directly to the overall report. I did a report based on this query that only shows the data and not the chart and was able to display the beginning date and ending date in a text box on the report. The next logical step was give the report the row source of the query which will get me the dates, only the chart now ceases to work and I am prompted for the dates twice. Any insight as to how I can resolve my problem?

Great advice thus far!
pbt1234

 

[OhioSteve]

1)Create a text field in a form to capture the values.

2)Open your query in design view. Put your cursor in the field's name. Then use the mouse to touch the "magic wand" icon. You will see an interface that helps to construct the query field's value (its called the "expression builder"). On the lower right you will see a folder containing your forms. Choose the appropriate form, and double-click the appropriate field in the form. Something like «Expr» Forms![Form2]![Text3] will appear. Change it to something like Forms![Form2]![Text3]. Now your query gets its parameter from the text field. Note that the text field could be a pulldown...that makes it much easier to control the user's choices.

3) In your report, print the value from the form field. To do this, select the appropriate field in your report and click the wand icon. Choose "expression builder" from the popup. Now use the expression builder just as you did previously.

 

[pbt1234]

My thanks go out to scriverb and ohiosteve! With input from both of you I was able to create a means to do this and learn a lot during my testing. I can now capture the dates, store them and show them on my reports. Thanks again to both of you for your input and help!!!

pbt1234