Passing date range Parameter to subreport

[John Buehler]

This one would seem easy but I have not figured it out for the past 3 days.

Like everyone else I have a main report with subreports. They are all
linked to the main report by a common index but one subreport I want to find
information pertaining to the corresponding month of the report not all
records. All records would be easy.

The user does data entry in a form, clicks a button, report prints everyone
is happy but now we want more info which involves a new subreport.

Okay lets get to meat of the subject:
In a form the user type a plethora of items which includes a date in one of
the fields, could be any date. I take that date run it up to a global
procedure in the module which gives me the first and last day of the month
and year in two separate variables.

I want that date range to be used in one of the subreports to give me
everything associated with this particular subjects records for the month
that corresponds with the original date entered.

I've tried to put unbound text boxes on the main report as well as the
subreport so the subreport's query can use those for parameters. I've tried
Select Where clauses but found that they can only take one date not a range.
I tried concatenating variables and text into one and passing that. I felt
like I got really close when I manually entered the parameters and it worked
but like everyone else on this newsgroup I'm missing it.

I don't want the user who is printing these reports to type the date ranges
a hundred times for all the reports, I've already got it done for them.

[Duane Hookom]

All you should have to do is allow users to enter the start and end dates on
the form. Then set the subreport record source query to use the controls on
the form like:

Between Forms!frmX!txtStart and Forms!frmX!txtEnd

--
Duane Hookom
MS Access MVP
--

"John Buehler" <john.b...@uchsc.edu> wrote in message
news:%23$cNMCGUG...@TK2MSFTNGP12.phx.gbl...

[Ken Snell (MVP)]

And keep the form open (can be hidden) while the report is being opened and
viewed/printed.

--

Ken Snell
<MS ACCESS MVP>

"Duane Hookom" <duane...@NoSpamHotmail.com> wrote in message
news:eXPhQRHU...@TK2MSFTNGP14.phx.gbl...

[John Buehler]

Well that does work but what if I want to print from a different form or I
want to print all reports for the first qtr. that would mean they get three
reports, one for each month?
Isn't there a more dynamic way of passing parameters? From my understanding
the subreports open before the main report so even if I place those two
field on the report it couldn't pass them.

Thanks for you help.

"Ken Snell (MVP)" <kthsne...@ncoomcastt.renaetl> wrote in message
news:%23I$kenIUG...@TK2MSFTNGP09.phx.gbl...

[Ken Snell (MVP)]

You can include calculated fields in the subreports' queries that read the
values from the form on which the dates / values are entered; for example:
StDateField: Forms!FormName!ControlName

Bind controls to those calculated fields so that you can use their values
in your subreport.

Or filter on those values directly in the query with the calculated fields.

Not sure why you would have a problem if you use a "different form" -- any
reason that form could not include similar textboxes or controls for
entering the parameter values?

Otherwise, you're going to have use VBA code in the Open event of the
subreport to create and assign the Recordsource with the desired query
string; and the code will need to get the dates from somewhere....
--

Ken Snell
<MS ACCESS MVP>

"John Buehler" <john.b...@uchsc.edu> wrote in message
news:OJVpPsOU...@tk2msftngp13.phx.gbl...

[John Buehler]

I would really like to learn how to do the whole recordset thing in VBA
which would get its parameters from a module. I'm just not sure how to do
it. I see examples of it but don't have a clue where to begin. On the
examples it show opening a database source but do you really need to do that
if the database is the one your using? I'm also working on a database that
is a front and back end, would I need to reference the back end with the
code? Would I need to take the record source out of the properties of the
report or what. This just starts me into an area I need to learn more
about. I was just hoping to pass a simple "Between startdate and enddate"
to a subreport. If I could of had the two variables drop into the subreport
query from the module it would have been so much easier.

"Ken Snell (MVP)" <kthsne...@ncoomcastt.renaetl> wrote in message

news:ustisXQU...@tk2msftngp13.phx.gbl...

[Duane Hookom]

Recordsets have little or nothing to do with 99.9% of all reports. What
would you expect to do with a recordset and a subreport?

--
Duane Hookom
MS Access MVP
--

"John Buehler" <john.b...@uchsc.edu> wrote in message

news:O1711$SUGHA...@tk2msftngp13.phx.gbl...

[John Buehler]

Well I thought that you might be able to assign the Recordsource with the
desired query string. So what does have 99.9% to do with this part?

"Duane Hookom" <duane...@NoSpamHotmail.com> wrote in message

news:O%23z7WyTU...@TK2MSFTNGP11.phx.gbl...

[Duane Hookom]

I assumed by "recordset" and "VBA" you meant a recordset like:
Dim rs As DAO.Recordset
This would rarely be used in a report.

You can change the SQL property of a saved query prior to opening your
report with subreports. The saved query could be used as the record source
of the subreport.

--
Duane Hookom
MS Access MVP
--

"John Buehler" <john.b...@uchsc.edu> wrote in message

news:O%23t3n9ZU...@TK2MSFTNGP09.phx.gbl...

[John Buehler]

Okay, could you give me an idea as to how to pass a date range to the query
with SQL. I read that you could only send a single date not a range.
I tried a Select, Where but the where didn't seem to work with a range.

"Duane Hookom" <duane...@NoSpamHotmail.com> wrote in message

news:u51JdSaU...@TK2MSFTNGP10.phx.gbl...

[Duane Hookom]

Ok, assuming you have a subreport with a record source of the saved query
"qselMyQuery" that you would like filtered by "txtStartDate" and
"txtEndDate" on the current form. The query has a SQL property of:

Select *
FROM tblSales;

You would add code to run in the form prior to opening the report with the
subreport:

Dim strSQL as String
strSQL = "SELECT * FROM tblSales WHERE 1=1 "
If Not IsNull(Me.txtStartDate) Then
strSQL= strSQL & " And [SalesDate]>=#" & _
Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
strSQL= strSQL & " And [SalesDate]<=#" & _
Me.txtEndDate & "# "
End If
CurrentDb.QueryDefs("qselMyQuery").SQL = strSQL

Then open the main report with the subreport. The subreport will be filtered
by the date range.

--
Duane Hookom
MS Access MVP
--

"John Buehler" <john.b...@uchsc.edu> wrote in message

news:OZ$LGXfUG...@TK2MSFTNGP11.phx.gbl...

[John Buehler]

Thank you very much

"Duane Hookom" <duane...@NoSpamHotmail.com> wrote in message

news:ewQaWMhU...@TK2MSFTNGP14.phx.gbl...

[Jane Walker]

What if you have a report that has 12 subreports based on 12 subforms and
queries and you need to change all of the dates to be the same range?

[Duane Hookom]

You either change the SQL of all 12 subreports or you enter criteria like:
Between Forms!frmX!txtStart and Forms!frmX!txtEnd

--
Duane Hookom
MS Access MVP
--

"Jane Walker" <JaneW...@discussions.microsoft.com> wrote in message
news:B472C93F-1FB5-42EB...@microsoft.com...

[ED007]

Duane:

I hope you come across this the original post was several days old. I am
trying to somthing similar. I have a two queries. One pulls the quality
control info for a given lot of material (criteria for this field is set to
[LOT] which gives a pop up box to ask for the lot in question. The second
query pulls the raw material lots used to produce the product lot (again
criteria for this field is set to [LOT]). I want prepare a report with the
first querry is shown in the top half and the second querry is shown in the
bottom half. I have this set up but when I run it it asks for [LOT] multiple
times.

I tried to set up a form that asks for lot and refier to that form using
forms!formX!lot in the criteria of the queries but it just gives me a text
box that asks for forms!formX!lot.

Am I missing somthing in the way the forms, queries, and reports are linked?

[Duane Hookom]

You must have the form open and a LOT value entered (selected from a combo
box?) prior to opening the report.

--
Duane Hookom
MS Access MVP

"ED007" <ED...@discussions.microsoft.com> wrote in message
news:3BEA4B36-EC93-45C3...@microsoft.com...