Pass Parameters from a Date Range Form to a Report

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:
DateRangeForm
pdvsaProject financeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
The report and its source query can access the textbox controls on the open form
Dale FyeOwner, Developing Solutions LLCCommented:
As you have already indicated, you already refer to those controls in your reports RecordSource.  I generally don't do it this way, perferring to use the OpenReport methods WhereCriteria argument so that I don't have to have the form open when I run the report in design view.  To do it this way,  You would remove those values from the query that is the reports recordsource and build a criteria string before opening the report, somehing like:
Private Sub cmdReport_Click

    dim strCriteria as string

    strCriteria = "([DateFieldName] >= #" & me.txtFromDate & "#) AND " _
                       & "([DateFieldName] < #" & cdate(me.txtThruDate) + 1 & "#)"
    docmd.openreport "ReportName", acViewPreview, , strCriteria

End Sub

Open in new window

But if you want to do it with the form and the references to the form in your query, then you need to either:

2.  Declare those form controls as parameters in your query.  You can do this by inserting a parameter declaration statement at the top of the queries SQL, like:

PARAMETERS [Forms]![MyForm]![txtDateFrom] DateTime, [Forms]![myForm]![txtDateTo] DateTime;
SELECT * FROM yourTable
WHERE [DateFieldName] >= [Forms]![MyForm]![txtDateFrom]
AND [DateFieldName] < [Forms]![MyForm]![txtDateFrom] + 1

3.  Another way to process this is to use the Eval function to force Access to evaluate the references to the form:

SELECT * FROM yourTable
WHERE [DateFieldName] >= Eval([Forms]![MyForm]![txtDateFrom])
AND [DateFieldName] < Eval([Forms]![MyForm]![txtDateFrom]) + 1)

When Iwant to use a form like that for multiple reports, I use the Report_Open event to open the form in acDialog mode.  Then, instead of having a cmd_Report button on the form to I put two buttons on the form, cmdCancel and cmdContinue.  In both cases I set the forms Tag property to either "Cancel" or "Continue", then hide the form.

Hiding the form returns control to the Report_Open event which then checks the Tag property of the form to determine whether it is Cancel.  If so, then I set the Cancel argument of the Form object to True and exit the subroutine (which closes the report).  If the Tag property was "Continue", I check to see whether txtDateFrom and txtDateTo contain values and construct a criteria string which I then apply to the forms Filter property.  This way, you can reuse that form for multiple reports and force the form to open whenever any of those reports are called.
Gustav BrockCIOCommented:
You can have two functions, one is shown here:
Public Function ReportDateFrom(Optional ByVal Value As Date) As Date

    Static LastDate As Date

    If Value = #00:00# Then
        ' Read date.
        If LastDate = #00:00# Then
            LastDate = Date
        End If
    Else
        ' Store date.
        LastDate = Value
    End If

    ReportDateFrom = Value

End Function

Open in new window

Now, before opening the report, set the dates:

    Call ReportDateFrom(Nz(Me!txtDateFrom.Value, Date))
    Call ReportDateTo(Nz(Me!txtDateTo.Value, Date))

Adjust your query to have this criteria:

    Where [YourReportDateField] Between ReportDateFrom() And ReportDateTo()

This works great for me.

/gustav

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

pdvsaProject financeAuthor Commented:
Dale, aikimark thank you.
Dale, I think you are closer to what I am after.  I do like your #3 option.  Its an approach I have been wanting to implement for quite some time. Putting the pieces together would be an issue for me.  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.  

I want to ask a question about #3 option (using the form for multiple reports)
There are 2 buttons:  one for Cancel and one for Continue
"Continue" seems to be Open Report to me.   Is that true?

I think the code below sets the Cancel argument of the Form object to True and exits the subroutine (which closes the report).

Private Sub Report_Open(Cancel As Integer)

      If CurrentProject.AllForms("frmDateRange").IsLoaded = False Then
        DoCmd.OpenForm "frmDateRange"
        MsgBox "Enter the appropriate fields, then click the report button"
        Cancel = True
    End If
   
End Sub
pdvsaProject financeAuthor Commented:
Gustav, didnt refresh before submitting.... let me look over.
pdvsaProject financeAuthor Commented:
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?  I believe, if true, then it would be similar to Dale's #3.   Not sure if I am following.  Grateful for your response and correct me if I am mistaken.
Gustav BrockCIOCommented:
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.

Also, you can have on form where you set the dates, and another where you open the report(s).

The thing I like is, that you isolate the names of your form and controls from the SQL of the queries for the reports.

/gustav
pdvsaProject financeAuthor Commented:
Ok I think I an following but have a followup on this :
"Also, you can have on form where you set the dates, and another where you open the report(s)."
==>i would need a button for each report, correct?
Gustav BrockCIOCommented:
That's up to you.
If you have many reports, you could list them in a list- or combox, select one, and click the button.

/gustav
pdvsaProject financeAuthor Commented:
Ok.  I will try to follow that.  I am going to be away from the computer for awhile though and might not get back until tomorrow.  

Any other comments are welcome.
Dale FyeOwner, Developing Solutions LLCCommented:
pdvsa,

In my option #3, both buttons change the Tag property of the form, then simply hide the form.

Me.visible = false

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.  I generally just hide it so that I can retrieve the value of the tag property from within the Report_Open event, something like:
    Docmd.openform "MyReportParameters",,,, acdialog   'check the # of commas
    if forms("myReportParameters").Tag = "Cancel" then
        Cancel = True
        Exit Sub
    Else
        Cancel = False   'not really needed because it is the default
        'some code here to define a filter for the report
    End if
    docmd.close acform, "MyReportParameters"

Open in new window

pdvsaProject financeAuthor Commented:
Gustav: would I place the OpenReport command like below?  

Private Sub Command17_Click()

    Call ReportDateFrom(Nz(Me!txtdatefrom.Value, Date))
    Call ReportDateTo(Nz(Me!txtDateTo.Value, Date))
    DoCmd.OpenReport "rptDraws_All", acViewReport, , , acDialog
    '  need to adjust query criteria to: Where [YourReportDateField] Between ReportDateFrom() And ReportDateTo()
End Sub

Open in new window



Does this look correct:
I have placed both functions in my Module "Global" functions part of db.
ReportDateTo:
Public Function ReportDateTo(Optional ByVal Value As Date) As Date
'Gustav (cactusdata):  9/13/16
    
    Static LastDate As Date

    If Value = #12:00:00 AM# Then
        ' Read date.
        If LastDate = #12:00:00 AM# Then
            LastDate = Date
        End If
    Else
        ' Store date.
        LastDate = Value
    End If

    ReportDateTo = Value

End Function

Open in new window

Gustav BrockCIOCommented:
Nearly. I made a mistake: The last line in both functions should read:

    ReportDateTo = LastDate

/gustav
pdvsaProject financeAuthor Commented:
Thank you Dale and Gustav.  I am a bit more familiar with Gustav's solution and I have successfully implemented it.   Grateful to both.
Gustav BrockCIOCommented:
You are welcome!

/gustav
pdvsaProject financeAuthor Commented:
I am going to ask a follow up on this though.   I need functionality to open the report and show all records if the txtDateFrom is Null.
Gustav BrockCIOCommented:
You could adjust the call:

    Call ReportDateFrom(Nz(Me!txtDateFrom.Value, #1/1/100#))

/gustav
pdvsaProject financeAuthor Commented:
Nice.  Worked perfectly.  I was about to press the send button on the follow up question.  Thank you sir... have a good day!
Gustav BrockCIOCommented:
Thanks!

/gustav
pdvsaProject financeAuthor Commented:
Gustav, sorry to pester you about this.

It seems that if the date ranges are NULL on the date range form, the report doesnt include all records (only some).  
In the below, the first line was modified based on your previous suggestion above, to show ALL records if the txtDateFrom was NULL.  
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.  
Maybe you know what exactly the issue is.  
when you have a sec... appreciate as always.
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.  

button on the frmDateRange:
Private Sub cmdFuturePast_Click()

    Call ReportDateFrom(Nz(Me!txtdatefrom.Value, #1/1/100#)) 
    Call ReportDateTo(Nz(Me!txtDateTo.Value, Date))
    DoCmd.OpenReport "rptFuture_Past", acViewReport, , , acDialog

End Sub

Open in new window


Also, possibly you see some issue in the report control source:
SELECT tblRepayment.ValueDate, tblDraws_Details1.ID, tblDraws_Details1.BankID, tblDraws_Details1.ID_facility, tblRepayment.PaymentMadeYN, tblRepayment.Amount
FROM tblDraws_Details1 INNER JOIN (tblRepayment LEFT JOIN tblCurrencyExchange ON tblRepayment.Currency = tblCurrencyExchange.CurrencyID) ON tblDraws_Details1.ID = tblRepayment.DrawIDrpmt
WHERE (((tblRepayment.ValueDate) Between ReportDateFrom() And ReportDateTo()))
ORDER BY tblRepayment.ValueDate, tblRepayment.PaymentMadeYN;

Open in new window

Gustav BrockCIOCommented:
If you have future dates, you could modify as follows:

    Call ReportDateTo(Nz(Me!txtDateTo.Value, #12/31/9999#))

However, if ValueDate can be Null, those records will always be excluded.
Or you could modify to:

    WHERE ((Nz(tblRepayment.ValueDate,Date()) Between ReportDateFrom() And ReportDateTo()))

/gustav
pdvsaProject financeAuthor Commented:
Gustav, that was exactly it.  The future dates were being excluded.  I used the first option.  Thank you once again...very grateful.
Gustav BrockCIOCommented:
Great. Step by step ...

/gustav
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.