Solved

Pass Parameters from a Date Range Form to a Report

Posted on 2016-09-13
23
38 Views
Last Modified: 2016-09-14
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
0
Comment
Question by:pdvsa
  • 11
  • 9
  • 2
  • +1
23 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 41795995
The report and its source query can access the textbox controls on the open form
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 total points
ID: 41796041
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.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 41796132
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
0
 

Author Comment

by:pdvsa
ID: 41796157
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
0
 

Author Comment

by:pdvsa
ID: 41796179
Gustav, didnt refresh before submitting.... let me look over.
0
 

Author Comment

by:pdvsa
ID: 41796311
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.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41796326
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
0
 

Author Comment

by:pdvsa
ID: 41796347
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?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41796364
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
0
 

Author Comment

by:pdvsa
ID: 41796387
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 41797038
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

0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:pdvsa
ID: 41797322
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

0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41797441
Nearly. I made a mistake: The last line in both functions should read:

    ReportDateTo = LastDate

/gustav
0
 

Author Closing Comment

by:pdvsa
ID: 41797627
Thank you Dale and Gustav.  I am a bit more familiar with Gustav's solution and I have successfully implemented it.   Grateful to both.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41797630
You are welcome!

/gustav
0
 

Author Comment

by:pdvsa
ID: 41797655
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.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41797660
You could adjust the call:

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

/gustav
0
 

Author Comment

by:pdvsa
ID: 41797667
Nice.  Worked perfectly.  I was about to press the send button on the follow up question.  Thank you sir... have a good day!
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41797685
Thanks!

/gustav
0
 

Author Comment

by:pdvsa
ID: 41798106
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

0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41798154
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
0
 

Author Comment

by:pdvsa
ID: 41798193
Gustav, that was exactly it.  The future dates were being excluded.  I used the first option.  Thank you once again...very grateful.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41798197
Great. Step by step ...

/gustav
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now