Solved

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

Posted on 2013-12-21
9
438 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 30

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 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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 47

Expert Comment

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

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!

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

749 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