Adding criteria or parameters to an existing report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report with 2 subreports.
Basically, the only thing I really need to do is just have a prompt for a
date range. I'm guessing that a query with the "Between [ ] And [ ]" will
need to be used.
But how do I pop this in an existing report?
Thanks!
 
One way is to change the recordsource of the report and add a where condition
Select * From MyTable where MyDate between [DateFrom] and [DateTo]
this will prompt with the two fields to enter values.

The other way which I prefer, is to create a form with two fields, DateFrom
AND DateTo, let the user enter the values, so when the user press the Ok
button it gives you more control

If isnull(me.datefrom) then
msgbox "Must eneter value"
else
if isnull(me.dateto) then
msgbox "must enter value"
else
docmd.OpenReport "ReportName",,,"DateField >= #" & me.datefrom
& "# AND DateField <= #" & me.DateTo & "#"
end if
end if
 
will the subreports follow along with the date range as well?

Ofer said:
One way is to change the recordsource of the report and add a where condition
Select * From MyTable where MyDate between [DateFrom] and [DateTo]
this will prompt with the two fields to enter values.

The other way which I prefer, is to create a form with two fields, DateFrom
AND DateTo, let the user enter the values, so when the user press the Ok
button it gives you more control

If isnull(me.datefrom) then
msgbox "Must eneter value"
else
if isnull(me.dateto) then
msgbox "must enter value"
else
docmd.OpenReport "ReportName",,,"DateField >= #" & me.datefrom
& "# AND DateField <= #" & me.DateTo & "#"
end if
end if

Jeffshex said:
I have a report with 2 subreports.
Basically, the only thing I really need to do is just have a prompt for a
date range. I'm guessing that a query with the "Between [ ] And [ ]" will
need to be used.
But how do I pop this in an existing report?
Thanks!
Click to expand...
Click to expand...
 
Subreports do not apply the same "where clause" as the main report. The
easiest method for filtering a range of dates on a subreport is to use a
criteria in the subreport's recordsource like:
Between Forms!frmDates!txtStart And Forms!frmDates!txtEnd

--
Duane Hookom
MS Access MVP
--

Jeffshex said:
will the subreports follow along with the date range as well?

Ofer said:
One way is to change the recordsource of the report and add a where
condition
Select * From MyTable where MyDate between [DateFrom] and [DateTo]
this will prompt with the two fields to enter values.

The other way which I prefer, is to create a form with two fields,
DateFrom
AND DateTo, let the user enter the values, so when the user press the Ok
button it gives you more control

If isnull(me.datefrom) then
msgbox "Must eneter value"
else
if isnull(me.dateto) then
msgbox "must enter value"
else
docmd.OpenReport "ReportName",,,"DateField >= #" &
me.datefrom
& "# AND DateField <= #" & me.DateTo & "#"
end if
end if

Jeffshex said:
I have a report with 2 subreports.
Basically, the only thing I really need to do is just have a prompt for
a
date range. I'm guessing that a query with the "Between [ ] And [ ]"
will
need to be used.
But how do I pop this in an existing report?
Thanks!
Click to expand...
Click to expand...
Click to expand...
 
No, it wont, in that case on the on open event of the report you can prompt
the user to enter parameters and then assign a recordsource to the reports
and sub reports

Private Sub Report_Open(Cancel As Integer)
Dim DateFrom
Dim DateTo
DateFrom = InputBox("DateFrom")
DateTo= InputBox("DateFrom")

Me.RecordSource = "select * from MyTable Where DateField >= #" & DateFrom &
"# AND DateField <= #" & DateTo & "#"

Me.SubFormName1.recordsource = "select * from MyTable2 Where DateField >= #"
& DateFrom & "# AND DateField <= #" & DateTo & "#"

Me.SubFormName2.recordsource = "select * from MyTable3 Where DateField >= #"
& DateFrom & "# AND DateField <= #" & DateTo & "#"

End Sub


Jeffshex said:
will the subreports follow along with the date range as well?

Ofer said:
One way is to change the recordsource of the report and add a where condition
Select * From MyTable where MyDate between [DateFrom] and [DateTo]
this will prompt with the two fields to enter values.

The other way which I prefer, is to create a form with two fields, DateFrom
AND DateTo, let the user enter the values, so when the user press the Ok
button it gives you more control

If isnull(me.datefrom) then
msgbox "Must eneter value"
else
if isnull(me.dateto) then
msgbox "must enter value"
else
docmd.OpenReport "ReportName",,,"DateField >= #" & me.datefrom
& "# AND DateField <= #" & me.DateTo & "#"
end if
end if

Jeffshex said:
I have a report with 2 subreports.
Basically, the only thing I really need to do is just have a prompt for a
date range. I'm guessing that a query with the "Between [ ] And [ ]" will
need to be used.
But how do I pop this in an existing report?
Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Well, I'm not sure if it really even matters or not...I'll explain what it is
and let you tell me if I need to be concerned with it.
Basically the main report will ask for a date range (for status reports) and
will total up hours for each department for that given date range. One of
the subreports is a find unmatched query which looks at a staff listing table
and will list the name of people who haven't turned in their report (based
off the main reports date range filter parameter).
Like I said, i'm not 100% sure if that subreport even needs to be concerned
with the date, since it is just looking for the names that are not present.
Let me know what you guys think.
Thanks again!

Ofer said:
No, it wont, in that case on the on open event of the report you can prompt
the user to enter parameters and then assign a recordsource to the reports
and sub reports

Private Sub Report_Open(Cancel As Integer)
Dim DateFrom
Dim DateTo
DateFrom = InputBox("DateFrom")
DateTo= InputBox("DateFrom")

Me.RecordSource = "select * from MyTable Where DateField >= #" & DateFrom &
"# AND DateField <= #" & DateTo & "#"

Me.SubFormName1.recordsource = "select * from MyTable2 Where DateField >= #"
& DateFrom & "# AND DateField <= #" & DateTo & "#"

Me.SubFormName2.recordsource = "select * from MyTable3 Where DateField >= #"
& DateFrom & "# AND DateField <= #" & DateTo & "#"

End Sub


Jeffshex said:
will the subreports follow along with the date range as well?

Ofer said:
One way is to change the recordsource of the report and add a where condition
Select * From MyTable where MyDate between [DateFrom] and [DateTo]
this will prompt with the two fields to enter values.

The other way which I prefer, is to create a form with two fields, DateFrom
AND DateTo, let the user enter the values, so when the user press the Ok
button it gives you more control

If isnull(me.datefrom) then
msgbox "Must eneter value"
else
if isnull(me.dateto) then
msgbox "must enter value"
else
docmd.OpenReport "ReportName",,,"DateField >= #" & me.datefrom
& "# AND DateField <= #" & me.DateTo & "#"
end if
end if

:

I have a report with 2 subreports.
Basically, the only thing I really need to do is just have a prompt for a
date range. I'm guessing that a query with the "Between [ ] And [ ]" will
need to be used.
But how do I pop this in an existing report?
Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Alright Ofer, I've been trying to use that statement you gave me and I guess
i'm not quite sure as to what I need to edit in there. I also got rid of one
of the 2 subreports.
Is this code going to filter both the main and subreport?
Here is what I currently have:
Private Sub Report_Open(Cancel As Integer)
Dim DateFrom
Dim DateTo
DateFrom = InputBox("DateFrom")
DateTo = InputBox("DateFrom")

Me.RecordSource = "select * from Project Status Where DateField >= #" &
DateFrom &
"# AND DateField <= #" & DateTo & "#"

Me.Staff Listing Without Matching Project Status.RecordSource = "select *
from Project Status Where DateField >= #"
& DateFrom & "# AND DateField <= #" & DateTo & "#"

End Sub
I know it's still not right because the code editor has lines of red text
still.

Ofer said:
No, it wont, in that case on the on open event of the report you can prompt
the user to enter parameters and then assign a recordsource to the reports
and sub reports

Private Sub Report_Open(Cancel As Integer)
Dim DateFrom
Dim DateTo
DateFrom = InputBox("DateFrom")
DateTo= InputBox("DateFrom")

Me.RecordSource = "select * from MyTable Where DateField >= #" & DateFrom &
"# AND DateField <= #" & DateTo & "#"

Me.SubFormName1.recordsource = "select * from MyTable2 Where DateField >= #"
& DateFrom & "# AND DateField <= #" & DateTo & "#"

Me.SubFormName2.recordsource = "select * from MyTable3 Where DateField >= #"
& DateFrom & "# AND DateField <= #" & DateTo & "#"

End Sub


Jeffshex said:
will the subreports follow along with the date range as well?

Ofer said:
One way is to change the recordsource of the report and add a where condition
Select * From MyTable where MyDate between [DateFrom] and [DateTo]
this will prompt with the two fields to enter values.

The other way which I prefer, is to create a form with two fields, DateFrom
AND DateTo, let the user enter the values, so when the user press the Ok
button it gives you more control

If isnull(me.datefrom) then
msgbox "Must eneter value"
else
if isnull(me.dateto) then
msgbox "must enter value"
else
docmd.OpenReport "ReportName",,,"DateField >= #" & me.datefrom
& "# AND DateField <= #" & me.DateTo & "#"
end if
end if

:

I have a report with 2 subreports.
Basically, the only thing I really need to do is just have a prompt for a
date range. I'm guessing that a query with the "Between [ ] And [ ]" will
need to be used.
But how do I pop this in an existing report?
Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top