LIKE criteria to return specific OR all data?

  • Thread starter Thread starter Larry
  • Start date Start date
L

Larry

I am trying to write a query that will return data based on what the user
selects for a parameter, or return all data if the user selects nothing.

The criteria has the following syntax: Like
[Forms]![frmExportRoster]![cboTeam] & "*"

So, if the user selects a team, that team will be displayed (work
beautifully).

BUT, if the user doesn't select a team, then it should return everything
since "Like *" should match everything. It turns out that it does not match
teams that are NULL. So it returns all records except for ones where a team
has not been specified.

Any ideas how I can fix this problem?
 
Well, I figured out a work-around.

I changed the query so that for these three values an empty string is
returned instead of a null, by making the column an expression :
IIF(IsNull([Team]),"",[Team])

Now it returns all data correctly.

Don't like doing it this way, but at least it works.
 
Change the criteria to

Like [Forms]![frmExportRoster]![cboTeam] & "*" OR
[Forms]![frmExportRoster]![cboTeam] is Null

(all in one criteria cell)

Access will rearrange this when you close the query, but it should still work
 
Back
Top