Restrict a report to a given date range

  • Thread starter Thread starter Robbro
  • Start date Start date
R

Robbro

I've been working on a report that summarizes a large amount of sales,
returns, cost and variance data for a given month. I am happy with the
results, but the next step is to take that to a YTD step. I think I can
accomplish that fairly easily, but the one addition that needs to be added
that I'm not sure how to accomplish is to be able to restrict it to certain
date ranges in the year.
All data has the month end date with it and the ranges will only need to be
consecutive (Jan to March, not Jan and March but not Feb for example).
What is the proper technique to accomplish this in access? All my data is
processed through a few queries then into 1 master querie which then feeds
the report.
Do I have some sort of control on the report that you can enter dates into
which feeds back to the querie somehow? Do I have pop ups when you open the
report that make you enter dates (the first option would be preferred over
this as you could enter dates "on the fly" and not have to close-reopen the
report for new dates) or is there some other way?
My knowledge is fairly basic, i've been playing with this report for about 3
months and learned a lot but its all basic querie and report knowledge, not
sure about this date filter stuff.

Thanks
 
I've been working on a report that summarizes a large amount of sales,
returns, cost and variance data for a given month. I am happy with the
results, but the next step is to take that to a YTD step. I think I can
accomplish that fairly easily, but the one addition that needs to be added
that I'm not sure how to accomplish is to be able to restrict it to certain
date ranges in the year.
All data has the month end date with it and the ranges will only need to be
consecutive (Jan to March, not Jan and March but not Feb for example).
What is the proper technique to accomplish this in access? All my data is
processed through a few queries then into 1 master querie which then feeds
the report.
Do I have some sort of control on the report that you can enter dates into
which feeds back to the querie somehow? Do I have pop ups when you open the
report that make you enter dates (the first option would be preferred over
this as you could enter dates "on the fly" and not have to close-reopen the
report for new dates) or is there some other way?
My knowledge is fairly basic, i've been playing with this report for about 3
months and learned a lot but its all basic querie and report knowledge, not
sure about this date filter stuff.

Thanks
Click to expand...

You're absolutely on the right track. Your query should be a "Parameter
Query". Create a little unbound form, frmCrit let's call it, with a textbox
named txtStart and another named txtEnd.

Use a criterion on your query such as
= [Forms]![frmCrit]![txtStart] AND <= [Forms]![frmCrit]![txtEnd]
Click to expand...

It's handy to put a command button on frmCrit to launch the report - that way
you can put in a range of dates, click the button to print, change the dates,
lather, rinse, repeat.
 
Use the Where argument of the Docmd.openreport method on a FORM. Build a
little form with one combo box on it with your selections, like print all,
print section, print section b, etc. The combo box wizard will ask you if you
want to type your own values to display in your combo or look up a table or
query. Select the type your own and enter your selections. Then in the
ONClick event of the combo box properties, type this in using your names.
Private Sub Combo0_Click()
Dim strwhere As String
Dim stDocument As String
stDocument = "07-09 plan review approval letter sent"
strwhere = "1=1 "
If Not IsNull(Me.Combo0) Then
strwhere = strwhere & " And [07-09 complete II_Area]=""" & _
Me.Combo0 & """"
End If
DoCmd.OpenReport stDocument, acPreview, , strwhere
DoCmd.Close acForm, "area form 3", acSaveNo
End Sub

The stdocument is your report name
the 0709 complete_area is the field you want to search on for your printing
print section a, print section b, etc
The me.combo is the combo number
The form is the name of your selection form
 
Back
Top