Repetetive Fields in Reports 1

[DennisORA]

I have written several Access reports based on multi-join queries, and have a question. For many of these reports there is a section on the upper right where I display the date range that the data is valid for. Is there some feature that would allow me to to use a subreport or embedded feature that would allow me to standardize this display, instead of copying and pasting for each report. Upon openning the report, the user is prompted for these dates, and the dates are used to in the query that creates the report. The dates filter the data rows, so that only the relevant rows are displayed. I am just wondering if there is a way to standardize the output, and to avoid the copying and pasting

Dennis

 

[dhookom]

IMHO parameter prompts are never good practice. One method I have implemented is a table in the front end MDB with "criteria" fields:

tblCriteria
==================
StartDate
EndDate

You can update these date on a form or subform (or with code). Then use this table in your report's record source to create your filter. You can then create a small subreport bound to tblCriteria to display the values.

You want to make sure there is one and only one record in this table. Don't allow additions to this table.

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]

 

[DennisORA]

Thank you very much. Your answer brings up two more questions. Why are parameter prompts to be avoided? And if I create a new seperate table for the dates, how do I join it to the other tables?

 

[dhookom]

I'm not sure why you have to ask why they should be avoided when you just found out they don't meet your needs ;-).
[ul]
[li]They don't allow defaults[/li]
[li]They don't allow picking from a list[/li]
[li]They don't allow seeing multiple values at once[/li]
[li]They aren't easily re-used[/li]
[li]You can't check their integrity[/li]
[li]You can't limit one based on the value of another[/li]
[li]they don't support input masks or formats[/li]
[li]they are annoying like pop-ups[/li]
[li]you rarely if ever find a parameter prompt type interface in any decent software application[/li]
[li]They can't be used as the record source of a common subreport across multiple other reports[/li]
[/ul]

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]

 

[GingerR]

I agree with Duane. Just make a form with Start Date and End Date text boxes, and buttons to open the reports. Then in the report headers, put your now standardized:

="Date Range: " & Forms!ReportForm!txtStartDate & " - " & Forms!ReportForm!txtEndDate

and put the criteria into your reports instead of prompting the user for the dates every time. And I don't even put the dates into a table. I just leave them as unbound text boxes on the form.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[dhookom]

I have found that having the dates in a table will work well in situations where you want to potentially enter the date in a number of different forms/controls or issues that require specification of parameter values like in Recordsets or crosstab queries.

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]

 

[GingerR]

I agree Duane, me too. I just don't bother if it's a little report interface, which I assumed this was...

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html