Solved

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

Posted on 2013-12-21
9
426 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 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

786 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