Report Criteria 2

[smicro]

I have a form which has a field called DateCalled. I wanted a report that would automatically print all records based on what date was showing in the DateCalled field. I have a report with criteria set to:
([Forms]![tblprospectssubform5]![DateCalled])
The report will always be printed while the form is open. What is occuring now is whenever I click the button to print it asks the user to enter parameter value. I wanted it not to ask for the paramter value and just print the records based on the date in the date field. If I do enter the date in the parameter field it works. Any help would be great, thanks.

 

[hneal98]

Are you sure the name of the field on the form is DateCalled?

 

[dhookom]

smicro,
Your form name "tblprospectssubform5" suggests your form is a subform on another form. If this is the case, your expression is wrong and would need to use the format:
Forms.frmMainForm.sbfrmControl.Form!txtYourBox

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[THWatson]

smicro
I'm assuming the set-up is like this.

Your report is based on a query. In the date column in the query you have the criteria you have indicated...
[Forms]![tblprospectssubform5]![DateCalled]
So you are expecting the report to print records from that date that you have selected in the form.

However, your criteria line suggests that this is referring to a text box on a subform, so perhaps it's a matter of properly referring to the text box.
The criteria would therefore be...

Forms!YourFormName.Form!tblprospectssubform5!DateCalled

Tom

 

[smicro]

That worked, thanks everyone!

 

[smicro]

Next I will try and tackle getting a message to ask for user input to allow them to select from specific dates

 

[THWatson]

smicro
On your form, you could create a list box, or combo box, that shows the dates from which to select. The list or combo box would include the dates that are in your table. If desired you could restrict the list or combo box to date values (for example, only dates within the past year).

Then you can either refer to the list or combo box value in your query criteria...or you can place that value in a text box and refer to the text box.

The other way to go on this is to remove the criteria from the query column and do everything from the form, by having a line something such as the following behind your command button...

DoCmd.OpenReport "YourReportName", acViewPreview, , "[YourDateFromTheTable] = Forms!YourFormName.Form!tblprospectssubform5!DateCalled"

Good luck with your project.
Tom

 

[dhookom]

I attempt to remove all "dynamic" criteria from queries that are the record sources of forms and reports. As Tom suggested, the criteria can be specified in the where clause of the DoCmd.OpenReport (or OpenForm) method.

Unlike Tom's suggested code, I use:

DoCmd.OpenReport "YourReportName", acViewPreview, _
 ,"[YourDateFromTheTable] = #" & Forms!YourFormName.Form!tblprospectssubform5!DateCalled & "#"

I believe this solution gives me much more flexibility in opening forms and reports.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[THWatson]

Duane is certainly correct! Using the hash marks (#) around dates is a better idea.

Tom

 

[smicro]

Great, I will give these suggestions a shot and let everyone know how it goes. Thanks for all the great knowledge!