?
Solved

Put WHERE criteria in click event i/o in the report's record source

Posted on 2013-12-21
9
Medium Priority
?
465 Views
Last Modified: 2013-12-24
I have rptDepartures with WHERE criteria saved in the report record source like so:

SELECT tblDepartures.*, tblEndUser.[End User], tblDepartures.Compliance_GOPS_YN, Projects.[Project Name], Projects.ContractAward
FROM (tblDepartures LEFT JOIN Projects ON tblDepartures.ProjectID = Projects.ID)

LEFT JOIN tblEndUser ON Projects.EndUserID = tblEndUser.EndUserID

WHERE (((tblEndUser.[End User]) Like [forms]![frmDepartures_filter].[txtClient] & "*" Or (tblEndUser.[End User]) Is Null) AND ((tblDepartures.EntryDate)>=[forms]![frmDepartures_filter]![txtDateFrom] And (tblDepartures.EntryDate)<[forms]![frmDepartures_filter]![txtDateTo]+1 And (tblDepartures.EntryDate) Is Not Null))

ORDER BY tblEndUser.[End User];

I know experts like to put the where conditions under the button's click event i/o in the report criteria.  So, now I want to move this criteria away from the record source of the report and move the WHERE condition to a button's macro event on the filter form (frmDepartures_filter).

I have played around with this for too long.  I cant do it. Maybe putting such criteria in the macro's where condition is not a good idea and should instead be put in the click event of the button on frmDepartures_filter.

any help is appreciated.
0
Comment
Question by:pdvsa
9 Comments
 
LVL 31

Expert Comment

by:hnasr
ID: 39734117
Attach the report, using dummy data, as it is now,

Then show the desired changes.

I understand you want to specify the criteria in the form, and clicking the button to run the report, filter for such criteria. Is this correct?
0
 
LVL 49

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 39734143
I'm one of those that prefers to have the report open to all records, which allows me the flexibility to use the OpenReport method and specify which records to include.  I do this, in the click event of a button on the form that call the report, syntax would look like:

Private Sub cmdReport_Click

    dim strCriteria as string

    strCriteria = "([End User] Like '" & me.[txtClient] & "*' Or [End User] Is Null) AND " _
                       & "[EntryDate]>=#" & me.[txtDateFrom] And " _
                       & "[EntryDate]<# & me.[txtDateTo]+1 And " _
                       & "[EntryDate] Is Not Null"
    docmd.openreport "reportname", acViewPreview, , strCriteria

End Sub

Hope this helps.
0
 

Author Comment

by:pdvsa
ID: 39734986
Thank you MX.  That's looks like the solution. I guess a macro might not be best suited for this.  Sent from phone...
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Closing Comment

by:pdvsa
ID: 39734988
Oopps. I meant to say fyed.  Sorry.
0
 
LVL 9
ID: 39735021
If you want to prompt the user for report selection criteria, you can do what we do - call a form from the report, instead of the other way around.

You can open a form from the report's Open event.  Open the form in Dialog mode so that the report waits for the form to be closed or hidden before it proceeds.  That way you can collect criteria from the user and build a Where clause for the report.  It also means that you can call the report directly - you don't need to call it from a form.  And the selection form is reusable - it can be called from multiple reports if they need the same criteria.

I've posted examples of this technique on our free J Street Downloads page at http://www.JStreetTech.com/downloads.

See "Report Selection Techniques".

Cheers,
Armen Stein
0
 

Author Comment

by:pdvsa
ID: 39737107
Fyed:

Why do you think I have this error?
If it matters, txtClient is an unbound field on the filter orm.
[EndUser] is a field in projects table and is text.

thank you.  I am sure you are busy doing other stuff so whenever you can get back to me appreciate it.

Armen, thank you for the tip.  I will add it to my laundry list of expert tips.

Error
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 39737271
Because one of us  ;-(

forgot to close the dates with #.  Try:

 strCriteria = "([End User] Like '" & me.[txtClient] & "*' Or [End User] Is Null) AND " _
                       & "[EntryDate]>=#" & me.[txtDateFrom] & "# And " _
                       & "[EntryDate]<# & me.[txtDateTo]+1 & "# And " _
                       & "[EntryDate] Is Not Null"
0
 

Author Comment

by:pdvsa
ID: 39738475
That was it...but I think there was a " missing in line 3.  

I might need to ask a follow up question. I need to add an "OR" statement to this.  I will try and figure it out though.


thank you
0
 

Author Comment

by:pdvsa
ID: 39738491
cant do it.  will ask a follow up in 5 min.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

809 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