LIKE Parameter Query

[Larry]

I have a query with 3 parameters. One or none of them will be filled in at
any one time, so the user can get all data, or just specific data based on
one of three columns.

To do this, I have used a parameter like this: Like [Enter Team] & "*"

So that if something is entered, it will be found, otherwise everything will
be returned. All three parameters are on the same line in the criteria
portion of the queries grid (they are ANDed).

The problem I am having is that if I enter nothing, in any parameter, I am
not getting all data back, like I thought I should. Since the asterisk(*)
matches everything, why isn't "Like *" returning all values? For some reason
it is not returning rows where there are NULLS in the columns with the
parameters.

Any idea what I should do here to get all data?

 

[JohnFol]

MyField=[Enter Team] Or [Enter Team] Is Null

FYI Null means "undefined" so it can never match.

 

[Larry]

The problem with this approach is that I actually have 3 criteria like this
for this query (team, group, location). The user specifies one of the three
ways (or none of them) to return a subset of the data (or all). If I put the
"or Is Null" on each of these, then I get back NULL data values for the
other two that were not specified, along with the value that was specified.

It DOES fix the problem for returning all data, but it hurts the conditions
where I'm trying to return specific data.

Thanks for the suggestion though.

MyField=[Enter Team] Or [Enter Team] Is Null

FYI Null means "undefined" so it can never match.

I have a query with 3 parameters. One or none of them will be filled in at
any one time, so the user can get all data, or just specific data based on
one of three columns.

To do this, I have used a parameter like this: Like [Enter Team] & "*"

So that if something is entered, it will be found, otherwise everything will
be returned. All three parameters are on the same line in the criteria
portion of the queries grid (they are ANDed).

The problem I am having is that if I enter nothing, in any parameter, I am
not getting all data back, like I thought I should. Since the asterisk(*)
matches everything, why isn't "Like *" returning all values? For some reason
it is not returning rows where there are NULLS in the columns with the
parameters.

Any idea what I should do here to get all data?

 

[Larry]

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.

The problem with this approach is that I actually have 3 criteria like this
for this query (team, group, location). The user specifies one of the three
ways (or none of them) to return a subset of the data (or all). If I put the
"or Is Null" on each of these, then I get back NULL data values for the
other two that were not specified, along with the value that was specified.

It DOES fix the problem for returning all data, but it hurts the conditions
where I'm trying to return specific data.

Thanks for the suggestion though.

MyField=[Enter Team] Or [Enter Team] Is Null

FYI Null means "undefined" so it can never match.

I have a query with 3 parameters. One or none of them will be filled

in

at

based

on

one of three columns.

To do this, I have used a parameter like this: Like [Enter Team] & "*"

So that if something is entered, it will be found, otherwise

everything
will

be returned. All three parameters are on the same line in the criteria
portion of the queries grid (they are ANDed).

The problem I am having is that if I enter nothing, in any parameter,

I

am