Re: Passing date range Parameter to subreport

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" <duanehookom@xxxxxxxxxxxxxxxxx> wrote in message
news:u51JdSaUGHA.1636@xxxxxxxxxxxxxxxxxxxxxxx
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.buehler@xxxxxxxxx> wrote in message
news:O%23t3n9ZUGHA.4348@xxxxxxxxxxxxxxxxxxxxxxx
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" <duanehookom@xxxxxxxxxxxxxxxxx> wrote in message
news:O%23z7WyTUGHA.1728@xxxxxxxxxxxxxxxxxxxxxxx
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.buehler@xxxxxxxxx> wrote in message
news:O1711$SUGHA.1572@xxxxxxxxxxxxxxxxxxxxxxx

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)" <kthsneisllis9@xxxxxxxxxxxxxxxxxx> wrote in message
news:ustisXQUGHA.1576@xxxxxxxxxxxxxxxxxxxxxxx
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.buehler@xxxxxxxxx> wrote in message
news:OJVpPsOUGHA.4608@xxxxxxxxxxxxxxxxxxxxxxx
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)" <kthsneisllis9@xxxxxxxxxxxxxxxxxx> wrote in message
news:%23I$kenIUGHA.5108@xxxxxxxxxxxxxxxxxxxxxxx
And keep the form open (can be hidden) while the report is being
opened
and viewed/printed.

--

Ken Snell
<MS ACCESS MVP>

"Duane Hookom" <duanehookom@xxxxxxxxxxxxxxxxx> wrote in message
news:eXPhQRHUGHA.2244@xxxxxxxxxxxxxxxxxxxxxxx
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.buehler@xxxxxxxxx> wrote in message
news:%23$cNMCGUGHA.5148@xxxxxxxxxxxxxxxxxxxxxxx
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.





















.