How do I create a query that will only apply the criteria when the field is populated??

[Lisa]

I have a table of projects in my database, and a table of employees in
a M2M relationship. I want to make a query that generates a list of
projects that any one person is involved with and then use the query to
create a report. I have created a form that allows the user to select
an Employee Name. They can then click the "Create Report" button. The
report uses a query to filter the data to display. In the query I'm
using the following criteria:

Like [forms]![frmReportFilter]![EmployeeName] & "*"

to pull the desired criteria from the form and create the report. This
works beautifully IF the Employee Name field is given a value in the
form.

The problem is that if the Employee Name field in the form is left
blank, the report creates a seperate page for each employee in the
entire database and filters out any projects that don't have any
employees assigned. The result I'd like to have is that IF the
Employee Name field is left blank in the form, that the query ignore
this criteria completely. I've tried adding "And Is Not Null" to the
criteria described above but I think I'm off base there. If I don't
use the asterisk in the above criteria, the filter doesn't work at all,
even if an EmployeeName is provided.

Am I making any sense here?? How do I create a query (for a report)
that will only apply the criteria when the EmployeeName field is
populated?

Thanks so much.

 

[Allen Browne]

The best solution is to use a command button on the form to open the report.
You can then remove the criteria from the query, and build the
WhereCondition string for the OpenReport action from the controls that are
not null.

Private Sub cmdPrint_Click()
Dim strWhere As String
If Not IsNull(Me.EmployeeName) Then
strWhere = "([EmployeeName] Like """ & Me.EmployeeName & "*"""
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

For other examples, see:
http://allenbrowne.com/casu-15.html
http://allenbrowne.com/casu-08.html

If you prefer to continue with your query, switch it to SQL View (View menu)
and edit the WHERE clause. For example if it reads:
WHERE (EmployeeName Like [forms]![frmReportFilter]![EmployeeName] & "*")
change it to:
WHERE (([forms]![frmReportFilter]![EmployeeName] Is Null)
OR (EmployeeName Like [forms]![frmReportFilter]![EmployeeName] & "*"))

 

[John Spencer]

Access is doing what you tell it to which is to match records that have the
value in [forms]![frmReportFilter]![EmployeeName] followed by zero or more
characters. So, if [forms]![frmReportFilter]![EmployeeName] is blank, that
means it will match every name in the database. Try changing your criteria.

Change
Like [forms]![frmReportFilter]![EmployeeName] & "*"
To
Like [forms]![frmReportFilter]![EmployeeName]
Or to
= [forms]![frmReportFilter]![EmployeeName] (The "=" will disappear from
the criteria box)