Reports based on date range

F

FSCK!

Hi -

I presently have a form used to open various reports. The form shows a
list of available reports (list populated by 'available reports'
table). The user selects desired report and clicks on 'Open Report'
button which has a "View Reports Macros.cmdOpenReport : On Click" macro
assigned to an 'OnClick' event which in turns does the following
"=[Forms]![View Reports]![lstReports]"

I would like to be able to specify a date range on this form so that
when I click on 'Open Report', it only shows a report with the
specified date range.

I am still in the process of learning access and I am having a
difficult time with this. Could anybody shed some light?

Many thanks
 
F

fredg

Hi -

I presently have a form used to open various reports. The form shows a
list of available reports (list populated by 'available reports'
table). The user selects desired report and clicks on 'Open Report'
button which has a "View Reports Macros.cmdOpenReport : On Click" macro
assigned to an 'OnClick' event which in turns does the following
"=[Forms]![View Reports]![lstReports]"

I would like to be able to specify a date range on this form so that
when I click on 'Open Report', it only shows a report with the
specified date range.

I am still in the process of learning access and I am having a
difficult time with this. Could anybody shed some light?

Many thanks
Click to expand...

Easiest way....
Add 2 unbound text controls to the form.
Name one control 'StartDate' and the other control 'EndDate'.

Then instead of using the macro to open the report, use code.
Here is how.

On the command button Click event line, instead of the macro name,
write on that click event line:

[Event Procedure]

Then click on the little button with the 3 dots that appears on that
line. When the code window opens, the cursor will be flashing between
2 already existing lines of code.
Between those 2 lines, write (all on one line):

DoCmd.OpenReport Me.lstReports, acViewPreview, , "[YourDateField]
between #" & Me![StartDate] & "# and #" & Me.[EndDate] & "#"

'Then on the next line...
Me.Visible = False

Change [YourDateField] to whatever the actual name of the date field
in the report is.

Enter the dates wanted in the form, select the report from the list
box, and click the command button. The report will open filtered to
the dates entered in the form. The form will become not visible.
I would suggest you add code to each Report's Close event (using the
same [Event Procedure] method as above):

' Either to close the form when the report closes:
DoCmd.Close acForm, Forms]![View Reports]

' Or to make the form visible again:
Forms]![View Reports].Visible = True
 
F

FSCK!

Hello -

Many thanks to the both of you for your help. Allen, I have followed
the instructions on your site and I have the 'event procedure' assigned
to my button and picking a report from my list box, but I am getting a:

runtime error 2103: "the report name 'Me.lstReports' you entered in
either property sheet or macro is misspelled or refers to a report that
doesn't exist."

It then takes me to the debug line at the end of your code.

Below is how my event procedure looks like (did not know what to put
under 'field' but I have tried leaving it blank as well:

Private Sub cmdOpenReport_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "Me.lstReports"
strField = "Reports"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " <= " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " >= " & Format(Me.txtStartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging purposes
only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere
End Sub

Thanks again


Allen said:
See:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

To have it open the report chosen in list box lstReports, change the line:
strReport = "rptSales"
to:
strReport = Me.lstReports

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

FSCK! said:
I presently have a form used to open various reports. The form shows a
list of available reports (list populated by 'available reports'
table). The user selects desired report and clicks on 'Open Report'
button which has a "View Reports Macros.cmdOpenReport : On Click" macro
assigned to an 'OnClick' event which in turns does the following
"=[Forms]![View Reports]![lstReports]"

I would like to be able to specify a date range on this form so that
when I click on 'Open Report', it only shows a report with the
specified date range.

I am still in the process of learning access and I am having a
difficult time with this. Could anybody shed some light?

Many thanks
Click to expand...
Click to expand...
 
A

Allen Browne

Drop the quotes:
strReport = Me.lstReports

The report is not named "Me.lstReports"
It is the selected name in the list box (hopefully.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

FSCK! said:
Hello -

Many thanks to the both of you for your help. Allen, I have followed
the instructions on your site and I have the 'event procedure' assigned
to my button and picking a report from my list box, but I am getting a:

runtime error 2103: "the report name 'Me.lstReports' you entered in
either property sheet or macro is misspelled or refers to a report that
doesn't exist."

It then takes me to the debug line at the end of your code.

Below is how my event procedure looks like (did not know what to put
under 'field' but I have tried leaving it blank as well:

Private Sub cmdOpenReport_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "Me.lstReports"
strField = "Reports"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " <= " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " >= " & Format(Me.txtStartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging purposes
only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere
End Sub

Thanks again


Allen said:
See:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

To have it open the report chosen in list box lstReports, change the
line:
strReport = "rptSales"
to:
strReport = Me.lstReports

FSCK! said:
I presently have a form used to open various reports. The form shows a
list of available reports (list populated by 'available reports'
table). The user selects desired report and clicks on 'Open Report'
button which has a "View Reports Macros.cmdOpenReport : On Click" macro
assigned to an 'OnClick' event which in turns does the following
"=[Forms]![View Reports]![lstReports]"

I would like to be able to specify a date range on this form so that
when I click on 'Open Report', it only shows a report with the
specified date range.

I am still in the process of learning access and I am having a
difficult time with this. Could anybody shed some light?
Click to expand...
Click to expand...
Click to expand...
 
F

FSCK!

Hi -

Removing the quotes seems to have resolve that issue. However, now I
get another error with the Strfield line. I have modified it to
reflect the name of the date field on my main table, which is "Opened
Date". That doesn't seem to work.

Allen said:
Drop the quotes:
strReport = Me.lstReports

The report is not named "Me.lstReports"
It is the selected name in the list box (hopefully.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

FSCK! said:
Hello -

Many thanks to the both of you for your help. Allen, I have followed
the instructions on your site and I have the 'event procedure' assigned
to my button and picking a report from my list box, but I am getting a:

runtime error 2103: "the report name 'Me.lstReports' you entered in
either property sheet or macro is misspelled or refers to a report that
doesn't exist."

It then takes me to the debug line at the end of your code.

Below is how my event procedure looks like (did not know what to put
under 'field' but I have tried leaving it blank as well:

Private Sub cmdOpenReport_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "Me.lstReports"
strField = "Reports"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " <= " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " >= " & Format(Me.txtStartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging purposes
only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere
End Sub

Thanks again


Allen said:
See:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

To have it open the report chosen in list box lstReports, change the
line:
strReport = "rptSales"
to:
strReport = Me.lstReports


I presently have a form used to open various reports. The form shows a
list of available reports (list populated by 'available reports'
table). The user selects desired report and clicks on 'Open Report'
button which has a "View Reports Macros.cmdOpenReport : On Click" macro
assigned to an 'OnClick' event which in turns does the following
"=[Forms]![View Reports]![lstReports]"

I would like to be able to specify a date range on this form so that
when I click on 'Open Report', it only shows a report with the
specified date range.

I am still in the process of learning access and I am having a
difficult time with this. Could anybody shed some light?
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

The line-wrap ocurred at just the wrong place, but I take it there is a
space in your field name?

If so, you must enclose the name in square brackets, like this:
strField = "[Opened Date]"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

FSCK! said:
Removing the quotes seems to have resolve that issue. However, now I
get another error with the Strfield line. I have modified it to
reflect the name of the date field on my main table, which is "Opened
Date". That doesn't seem to work.

Allen said:
Drop the quotes:
strReport = Me.lstReports

The report is not named "Me.lstReports"
It is the selected name in the list box (hopefully.)

FSCK! said:
Hello -

Many thanks to the both of you for your help. Allen, I have followed
the instructions on your site and I have the 'event procedure' assigned
to my button and picking a report from my list box, but I am getting a:

runtime error 2103: "the report name 'Me.lstReports' you entered in
either property sheet or macro is misspelled or refers to a report that
doesn't exist."

It then takes me to the debug line at the end of your code.

Below is how my event procedure looks like (did not know what to put
under 'field' but I have tried leaving it blank as well:

Private Sub cmdOpenReport_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "Me.lstReports"
strField = "Reports"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " <= " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " >= " & Format(Me.txtStartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging purposes
only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere
End Sub

Thanks again


Allen Browne wrote:
See:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

To have it open the report chosen in list box lstReports, change the
line:
strReport = "rptSales"
to:
strReport = Me.lstReports


I presently have a form used to open various reports. The form shows
a
list of available reports (list populated by 'available reports'
table). The user selects desired report and clicks on 'Open Report'
button which has a "View Reports Macros.cmdOpenReport : On Click"
macro
assigned to an 'OnClick' event which in turns does the following
"=[Forms]![View Reports]![lstReports]"

I would like to be able to specify a date range on this form so that
when I click on 'Open Report', it only shows a report with the
specified date range.

I am still in the process of learning access and I am having a
difficult time with this. Could anybody shed some light?
Click to expand...
Click to expand...
Click to expand...
 
F

FSCK!

Hello -

I want to thank you immensely for your help. It works perfect now
after following your suggestions. I went ahead and added the event
procedure to the dlb-click events of each report shown on the list box
as well, this way it works if I double clicks the report instead of the
button.

Thanks again, it has been a great learning experience.

-Robert

Allen said:
The line-wrap ocurred at just the wrong place, but I take it there is a
space in your field name?

If so, you must enclose the name in square brackets, like this:
strField = "[Opened Date]"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

FSCK! said:
Removing the quotes seems to have resolve that issue. However, now I
get another error with the Strfield line. I have modified it to
reflect the name of the date field on my main table, which is "Opened
Date". That doesn't seem to work.

Allen said:
Drop the quotes:
strReport = Me.lstReports

The report is not named "Me.lstReports"
It is the selected name in the list box (hopefully.)

Hello -

Many thanks to the both of you for your help. Allen, I have followed
the instructions on your site and I have the 'event procedure' assigned
to my button and picking a report from my list box, but I am getting a:

runtime error 2103: "the report name 'Me.lstReports' you entered in
either property sheet or macro is misspelled or refers to a report that
doesn't exist."

It then takes me to the debug line at the end of your code.

Below is how my event procedure looks like (did not know what to put
under 'field' but I have tried leaving it blank as well:

Private Sub cmdOpenReport_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "Me.lstReports"
strField = "Reports"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " <= " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " >= " & Format(Me.txtStartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging purposes
only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere
End Sub

Thanks again


Allen Browne wrote:
See:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

To have it open the report chosen in list box lstReports, change the
line:
strReport = "rptSales"
to:
strReport = Me.lstReports


I presently have a form used to open various reports. The form shows
a
list of available reports (list populated by 'available reports'
table). The user selects desired report and clicks on 'Open Report'
button which has a "View Reports Macros.cmdOpenReport : On Click"
macro
assigned to an 'OnClick' event which in turns does the following
"=[Forms]![View Reports]![lstReports]"

I would like to be able to specify a date range on this form so that
when I click on 'Open Report', it only shows a report with the
specified date range.

I am still in the process of learning access and I am having a
difficult time with this. Could anybody shed some light?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Toolbars In Access 2007 Reports 1
Visual Basic for Open Form, set value of object as "" 1
Contact Management Database Template 6
Can I use the same form for multiple reports 10
Crosstab Date Range in Reports 3
Date Range Query. Criteria based in a table. 4
Filter report based on continuous form 1
Trying to Build a Main Menu form with Command Buttons 1

Top