<div>
The report and its source query can access the textbox controls on the open form
</div>


<div>
Dale, aikimark thank you. <br />
Dale, I think you are closer to what I am after. &nbsp;I do like your #3 option. &nbsp;Its an approach I have been wanting to implement for quite some time. Putting the pieces together would be an issue for me. &nbsp;I think #1 is something I can handle a little better and will probably then need multiple forms i/o of one form like in your #3 option. &nbsp;<br />
<br />
I want to ask a question about #3 option (using the form for multiple reports)<br />
There are 2 buttons: &nbsp;one for Cancel and one for Continue<br />
&quot;Continue&quot; seems to be Open Report to me. &nbsp; Is that true?<br />
<br />
I think the code below sets the Cancel argument of the Form object to True and exits the subroutine (which closes the report).<br />
<br />
Private Sub Report_Open(Cancel As Integer)<br />
<br />
&nbsp; &nbsp; &nbsp; If CurrentProject.AllForms(&quot;f<wbr />rmDateRang<wbr />e&quot;).IsLoad<wbr />ed = False Then<br />
&nbsp; &nbsp; &nbsp; &nbsp; DoCmd.OpenForm &quot;frmDateRange&quot;<br />
&nbsp; &nbsp; &nbsp; &nbsp; MsgBox &quot;Enter the appropriate fields, then click the report button&quot;<br />
&nbsp; &nbsp; &nbsp; &nbsp; Cancel = True<br />
&nbsp; &nbsp; End If<br />
&nbsp; &nbsp; <br />
End Sub
</div>


<div>
Gustav, didnt refresh before submitting.... let me look over.
</div>


<div>
Gustav, that can be applied to multiple reports meaning that i have one frmDateRange and use the reports OnOpen event to check if the frmDateRange is open? &nbsp;I believe, if true, then it would be similar to Dale's #3. &nbsp; Not sure if I am following. &nbsp;Grateful for your response and correct me if I am mistaken.
</div>


<div>
Yes, if you have the form open, you can have a button to call the two functions and open any selected report as long as you adjust the criteria for the query you use as source for the report. <br />
<br />
Also, you can have on form where you set the dates, and another where you open the report(s).<br />
<br />
The thing I like is, that you isolate the names of your form and controls from the SQL of the queries for the reports.<br />
<br />
/gustav
</div>


<div>
Ok I think I an following but have a followup on this :<br />
&quot;Also, you can have on form where you set the dates, and another where you open the report(s).&quot;<br />
==&gt;i would need a button for each report, correct?
</div>


<div>
That's up to you. <br />
If you have many reports, you could list them in a list- or combox, select one, and click the button.<br />
<br />
/gustav
</div>


<div>
Ok. &nbsp;I will try to follow that. &nbsp;I am going to be away from the computer for awhile though and might not get back until tomorrow. &nbsp;<br />
<br />
Any other comments are welcome.
</div>


<div>
pdvsa,<br />
<br />
In my option #3, both buttons change the Tag property of the form, then simply hide the form.<br />
<br />
Me.visible = false<br />
<br />
Because you use the acDialog window mode to open the form from the Report_Open event, the code in the open event pauses until the form is closed or hidden. &nbsp;I generally just hide it so that I can retrieve the value of the tag property from within the Report_Open event, something like:<br />

<pre><code class="code-1 nolinks" id="code-20-41797038-1"><span>    Docmd.openform &quot;MyReportParameters&quot;,,,, acdialog   'check the # of commas</span>
<span>    if forms(&quot;myReportParameters&quot;).Tag = &quot;Cancel&quot; then</span>
<span>        Cancel = True</span>
<span>        Exit Sub</span>
<span>    Else</span>
<span>        Cancel = False   'not really needed because it is the default</span>
<span>        'some code here to define a filter for the report</span>
<span>    End if</span>
<span>    docmd.close acform, &quot;MyReportParameters&quot;</span>
</code></pre>
</div>


<div>
Gustav: would I place the OpenReport command like below? &nbsp;<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-41797322-1"><span>Private Sub Command17_Click()</span>
<span></span>
<span>    Call ReportDateFrom(Nz(Me!txtdatefrom.Value, Date))</span>
<span>    Call ReportDateTo(Nz(Me!txtDateTo.Value, Date))</span>
<span>    DoCmd.OpenReport &quot;rptDraws_All&quot;, acViewReport, , , acDialog</span>
<span>    '  need to adjust query criteria to: Where [YourReportDateField] Between ReportDateFrom() And ReportDateTo()</span>
<span>End Sub</span>
</code></pre>
<br />
<br />
Does this look correct:<br />
I have placed <b><u>both </u></b>functions in my Module &quot;Global&quot; functions part of db. <br />
ReportDateTo:<br />

<pre><code class="code-10 nolinks" id="code-20-41797322-2"><span>Public Function ReportDateTo(Optional ByVal Value As Date) As Date</span>
<span>'Gustav (cactusdata):  9/13/16</span>
<span>    </span>
<span>    Static LastDate As Date</span>
<span></span>
<span>    If Value = #12:00:00 AM# Then</span>
<span>        ' Read date.</span>
<span>        If LastDate = #12:00:00 AM# Then</span>
<span>            LastDate = Date</span>
<span>        End If</span>
<span>    Else</span>
<span>        ' Store date.</span>
<span>        LastDate = Value</span>
<span>    End If</span>
<span></span>
<span>    ReportDateTo = Value</span>
<span></span>
<span>End Function</span>
</code></pre>
</div>


<div>
Nearly. I made a mistake: The last line in both functions should read:<br />
<br />
&nbsp; &nbsp; ReportDateTo = LastDate<br />
<br />
/gustav
</div>


<div>
Thank you Dale and Gustav. &nbsp;I am a bit more familiar with Gustav's solution and I have successfully implemented it. &nbsp; Grateful to both.
</div>


<div>
You are welcome!<br />
<br />
/gustav
</div>


<div>
I am going to ask a follow up on this though. &nbsp; I need functionality to open the report and show all records if the txtDateFrom is Null.
</div>


<div>
You could adjust the call:<br />
<br />
&nbsp; &nbsp; Call ReportDateFrom(Nz(Me!txtDa<wbr />teFrom.Val<wbr />ue, #1/1/100#))<br />
<br />
/gustav
</div>


<div>
Nice. &nbsp;Worked perfectly. &nbsp;I was about to press the send button on the follow up question. &nbsp;Thank you sir... have a good day!
</div>


<div>
Thanks!<br />
<br />
/gustav
</div>


<div>
Gustav, sorry to pester you about this.<br />
<br />
It seems that if the date ranges are NULL on the date range form, the report doesnt include all records (only some). &nbsp;<br />
In the below, the first line was modified based on your previous suggestion above, to show ALL records if the txtDateFrom was NULL. &nbsp;<br />
I thought that maybe I needed the issue was that I needed to modify the line below with #1/1/100# as well (the txtDateTo line) but that returned no records. &nbsp;<br />
Maybe you know what exactly the issue is. &nbsp;<br />
when you have a sec... appreciate as always. <br />
Just wondering if you see something obvious to the issue and I am not wishing much more than 1 minute of your time because I can live without this fix. &nbsp;<br />
<br />
<b>button on the frmDateRange:</b><br />

<pre><code class="code-1 nolinks" id="code-20-41798106-1"><span>Private Sub cmdFuturePast_Click()</span>
<span></span>
<span>    Call ReportDateFrom(Nz(Me!txtdatefrom.Value, #1/1/100#)) </span>
<span>    Call ReportDateTo(Nz(Me!txtDateTo.Value, Date))</span>
<span>    DoCmd.OpenReport &quot;rptFuture_Past&quot;, acViewReport, , , acDialog</span>
<span></span>
<span>End Sub</span>
</code></pre>
<br />
<b>Also, possibly you see some issue in the report control source:</b><br />

<pre><code class="code-1 nolinks" id="code-20-41798106-2"><span>SELECT tblRepayment.ValueDate, tblDraws_Details1.ID, tblDraws_Details1.BankID, tblDraws_Details1.ID_facility, tblRepayment.PaymentMadeYN, tblRepayment.Amount</span>
<span>FROM tblDraws_Details1 INNER JOIN (tblRepayment LEFT JOIN tblCurrencyExchange ON tblRepayment.Currency = tblCurrencyExchange.CurrencyID) ON tblDraws_Details1.ID = tblRepayment.DrawIDrpmt</span>
<span>WHERE (((tblRepayment.ValueDate) Between ReportDateFrom() And ReportDateTo()))</span>
<span>ORDER BY tblRepayment.ValueDate, tblRepayment.PaymentMadeYN;</span>
</code></pre>
</div>


<div>
If you have future dates, you could modify as follows:<br />
<br />
&nbsp; &nbsp; Call ReportDateTo(Nz(Me!txtDate<wbr />To.Value, #12/31/9999#))<br />
<br />
However, if <i>ValueDate </i>can be Null, those records will always be excluded. <br />
Or you could modify to:<br />
<br />
&nbsp; &nbsp; WHERE ((Nz(tblRepayment.ValueDat<wbr />e,Date()) Between ReportDateFrom() And ReportDateTo()))<br />
<br />
/gustav
</div>


<div>
Gustav, that was exactly it. &nbsp;The future dates were being excluded. &nbsp;I used the first option. &nbsp;Thank you once again...very grateful.
</div>


<div>
Great. Step by step ...<br />
<br />
/gustav
</div>


<div>
<div class="content wysiwyg-content">
Experts,<br />
<br />
I have a frmDateRange and have 2 fields: &nbsp;txtDateFrom and txtDateTo<br />
I have a button on frmDateRange that opens rptAvailable.<br />
In the rptAvailable record source, the query parameters refer to the form's values as found in frmDateRange.txtDateFrom and frmDateRange.txtDateTo.<br />
(ie: &gt;=[forms]![frmDateRange]![<wbr />txtDateFro<wbr />m] And &lt;[forms]![frmDateRange]![t<wbr />xtDateTo]+<wbr />1 And Is Not Null)<br />
<br />
I know it is better to pass the date ranges via the button on the form i/o loading the criteria into the report via a parameter query. <br />
<br />
How can I load the criteria and open the report by way of the button on the frmDateRange?<br />
<br />
<u>following is what I think you might need:</u><br />
frmDateRange<br />
&nbsp; &nbsp; &nbsp;txtDateFrom<br />
&nbsp; &nbsp; &nbsp;txtDateTo<br />
rptAvailable<br />
<br />
<br />
Thank you<br />
let me know if you need something else<br />
<br />
here is the frmDateRange:<br />
<a href="https://filedb.experts-exchange.com/incoming/2016/09_w38/1116361/Untitled.jpg" target="_blank" title="DateRangeForm (21 KB)"><img alt="DateRangeForm" class="file-block lazyload" style="max-width: 424px;" data-src="https://filedb.experts-exchange.com/incoming/2016/09_w38/1116361/Untitled.jpg" /></a>
</div>
</div>


Untitled.jpg

Experts,

I have a frmDateRange and have 2 fields:  txtDateFrom and txtDateTo
I have a button on frmDateRange that opens rptAvailable.
In the rptAvailable record source, the query parameters refer to the form's values as found in frmDateRange.txtDateFrom and frmDateRange.txtDateTo.
(ie: >=[forms]![frmDateRange]![txtDateFrom] And <[forms]![frmDateRange]![txtDateTo]+1 And Is Not Null)

I know it is better to pass the date ranges via the button on the form i/o loading the criteria into the report via a parameter query. 

How can I load the criteria and open the report by way of the button on the frmDateRange?

following is what I think you might need:
frmDateRange
     txtDateFrom
     txtDateTo
rptAvailable


Thank you
let me know if you need something else

here is the frmDateRange:


Pass Parameters from a Date Range Form to a Report

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.