Changing criteria for Reports or queries.

[Michelle]

I have several people that do not know anything about Access (and are very
afraid of it) but have to run reports weekly and monthly. Some reports have
a few queries linked to them and some reports have subreports in them. Is
there a way whether a macro or parameter or something else that I can put
into the reports or queries so that the dates will either automatically
change for them or they can just put it in once for a report instead of
changing all the queiries? Basically is there a majic button I could put in
for them that would help.

 

[Duane Hookom]

Generally, you should open reports and forms from command buttons (or control
events) on a form. Users should never see the database window.

You can place text boxes on your form for users to enter start and end
dates. Then either use code to build a WHERE CONDITION in the
DoCmd.OpenReport method or set criteria in your Record Sources like:
Between Forms!frmDates!txtStartDate and Forms!frmDates!txtEndDate

 

[BruceM]

The criteria for a date field could be:
Between DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)
This would return all records for the current month.
You can have the user define a query's criteria by setting the date field
criteria to:
Between [Start Date] And [End Date]
Use whatever prompts you choose within the square brackets. The user will
be prompted for dates when opening a report bound to the query.
You could also have an unbound form (I will call it frmParam) with StartDate
and EndDate text boxes. The user would open the form, enter the dates, then
click a button to open the report. The report is bound to the same query as
in the example above, except the criteria would be:
Between [Forms]![frmParam]![StartDate] And [Forms]![frmParam]![EndDate]
You have asked an open-ended question, so I will wait to see if you have a
preference about how to manage this before I provide details about any one
method. Actually, I expect the first two are pretty clear.
In any case, there are lots of ways to avoid asking users to negotiate a
query design grid.