J
Joyce
I have a report that I want to be able to generate with a
start and end date. How would I do this. Thank you.
start and end date. How would I do this. Thank you.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
F
fredg
I have a report that I want to be able to generate with a
start and end date. How would I do this. Thank you.
There are several ways.
One is to base the report upon a query.
As query criteria in the Date field write:
Between [Enter Start Date] AND [Enter End Date]
Run the report. You will be prompted to enter the dates.
S
Steve Schapel
Joyce,
As Fred mentioned, there are various approaches. Another option,
similar to Fred's suggestion but perhaps more user-friendly, is to put
two unbound textboxes on a form, where you enter the start date and
end date criteria, and then in your criteria in the query, refer to
these controls using syntax such as...
Between [Forms]![NameOfForm]![StartDate] And
[Forms]![NameOfForm]![EndDate]
- Steve Schapel, Microsoft Access MVP
As Fred mentioned, there are various approaches. Another option,
similar to Fred's suggestion but perhaps more user-friendly, is to put
two unbound textboxes on a form, where you enter the start date and
end date criteria, and then in your criteria in the query, refer to
these controls using syntax such as...
Between [Forms]![NameOfForm]![StartDate] And
[Forms]![NameOfForm]![EndDate]
- Steve Schapel, Microsoft Access MVP
D
Duane Hookom
I think the next progression in this thread is to use code with a where
clause. This removes all (or most) of the dynamic criteria from the query.
Assuming Steve's form with a command button to open the report. The code of
the command button would be something like:
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.StartDate) Then
strWhere = strWhere & " And [DateField] >=#" & _
Me.StartDate & "# "
And If
If Not IsNull(Me.EndDate) Then
strWhere = strWhere & " And [DateField] <=#" & _
Me.EndDate & "# "
And If
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere
This method actually sets and applies the filter property of the report.
--
Duane Hookom
MS Access MVP
clause. This removes all (or most) of the dynamic criteria from the query.
Assuming Steve's form with a command button to open the report. The code of
the command button would be something like:
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.StartDate) Then
strWhere = strWhere & " And [DateField] >=#" & _
Me.StartDate & "# "
And If
If Not IsNull(Me.EndDate) Then
strWhere = strWhere & " And [DateField] <=#" & _
Me.EndDate & "# "
And If
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere
This method actually sets and applies the filter property of the report.
--
Duane Hookom
MS Access MVP
Steve Schapel said:Joyce,
As Fred mentioned, there are various approaches. Another option,
similar to Fred's suggestion but perhaps more user-friendly, is to put
two unbound textboxes on a form, where you enter the start date and
end date criteria, and then in your criteria in the query, refer to
these controls using syntax such as...
Between [Forms]![NameOfForm]![StartDate] And
[Forms]![NameOfForm]![EndDate]
- Steve Schapel, Microsoft Access MVP
There are several ways.
One is to base the report upon a query.
As query criteria in the Date field write:
Between [Enter Start Date] AND [Enter End Date]
Run the report. You will be prompted to enter the dates.
J
Joyce
to print on the report so now I'm using a form to capture-----Original Message-----
I have a report that I want to be able to generate with a
start and end date. How would I do this. Thank you.
There are several ways.
One is to base the report upon a query.
As query criteria in the Date field write:
Between [Enter Start Date] AND [Enter End Date]
Run the report. You will be prompted to enter the dates.
--
Fred
Please reply only to this newsgroup.
I do not reply to personal e-mail.
.
I tried using a query, but then I could not get the dates
the date ranges. Now those dates appear on the report,
but the report is bring back inaccurate information. The
roport is not reading the search criteria. Any help you
can give me with that would be appriciated. Thank you.
F
fredg
to print on the report so now I'm using a form to capture-----Original Message-----
I have a report that I want to be able to generate with a
start and end date. How would I do this. Thank you.
There are several ways.
One is to base the report upon a query.
As query criteria in the Date field write:
Between [Enter Start Date] AND [Enter End Date]
Run the report. You will be prompted to enter the dates.
--
Fred
Please reply only to this newsgroup.
I do not reply to personal e-mail.
.
I tried using a query, but then I could not get the dates
the date ranges. Now those dates appear on the report,
but the report is bring back inaccurate information. The
roport is not reading the search criteria. Any help you
can give me with that would be appriciated. Thank you.
If the query criteria is written as:
Between [Enter Start Date] AND [Enter End Date]
all you need do to show the range in the report is to add an unbound
control to the report header.
Set it's control source to something like:
="For sales between " & [Enter Start Date] " AND " & [Enter End Date]
*** The text within the brackets must be identical to the query
bracketed text. ***
If you wish to use a form to enter the date range, all you need do is
change the query criteria from the above to:
Between forms!FormName!StartDate and forms!FormName!EndDate
Now in the report set that unbound control's control source to:
="For sales between " & forms!FormName!StartDate & " AND " &
forms!FormName!EndDate
Note: The form MUST be open when the report is run.
The normal method to then close the form is to use the Report's Close
event:
DoCmd.Close acForm, "FormName"