[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2013-12-21
9
Medium Priority
?
463 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 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 48

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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

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 48

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

650 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