A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Restrict a report to a given date range



 
 
Thread Tools Display Modes
  #1  
Old March 16th, 2010, 03:42 PM posted to microsoft.public.access.gettingstarted
Robbro
external usenet poster
 
Posts: 82
Default Restrict a report to a given date range

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

  #2  
Old March 16th, 2010, 05:58 PM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Restrict a report to a given date range

On Tue, 16 Mar 2010 07:42:02 -0700, Robbro
wrote:

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


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]


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.
--

John W. Vinson [MVP]
  #3  
Old March 16th, 2010, 06:35 PM posted to microsoft.public.access.gettingstarted
Golfinray
external usenet poster
 
Posts: 1,597
Default Restrict a report to a given date range

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
--
Milton Purdy
ACCESS
State of Arkansas


"Robbro" wrote:

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:46 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.