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

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.
pdvsaProject financeAsked:
Who is Participating?
Dale FyeConnect With a Mentor Commented:
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.
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?
pdvsaProject financeAuthor Commented:
Thank you MX.  That's looks like the solution. I guess a macro might not be best suited for this.  Sent from phone...
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

pdvsaProject financeAuthor Commented:
Oopps. I meant to say fyed.  Sorry.
Armen Stein - Microsoft Access MVP since 2006PresidentCommented:
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".

Armen Stein
pdvsaProject financeAuthor Commented:

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.

Dale FyeCommented:
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"
pdvsaProject financeAuthor Commented:
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
pdvsaProject financeAuthor Commented:
cant do it.  will ask a follow up in 5 min.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.