Having Trouble With Query Criteria

[David Portwood]

I have a query where I want to select records based on a name in the
"Assigned To" field. The user selects a name from a list on a criteria form
and the query takes the name and selects records with that name in that
field. This part is easy.

However, if the user does not select a name on the criteria form, I want the
query to return ALL records.

I don't know how to choose between these two possibilities. I can set up the
query to run one or the other, but I don't know how to conditionally choose
between them.

Does anybody know how to do this?

 

[Guest]

David,

in that case I always use the following in my query:

like [your reference field here] &"*"

That way if the user doesn't select anything he will see al the records..

hth

 

[Pete]

Items in select box
Make the default LIKE "*" so if it doesn't get anything else it gives you
all but answers the request from the query parameter.

 

[John W. Vinson]

I have a query where I want to select records based on a name in the
"Assigned To" field. The user selects a name from a list on a criteria form
and the query takes the name and selects records with that name in that
field. This part is easy.

However, if the user does not select a name on the criteria form, I want the
query to return ALL records.

I don't know how to choose between these two possibilities. I can set up the
query to run one or the other, but I don't know how to conditionally choose
between them.

Does anybody know how to do this?

Pete and Maurice suggested using LIKE - and this will work, but with some
reservations. For instance, if the name selected is SMITH, you will see
records for SMITH, SMITHSON, SMITHERS, and so on if you're searching a text
field. Not what you want!

An alternative is to use

[namefield] = [Forms]![NameOfForm]![NameOfControl] OR
[Forms]![NameOfForm]![NameOfControl] IS NULL

John W. Vinson [MVP]

 

[Guest]

Hi John,

And so right you are. You must have the possibility to set the criteria to a
specific option. S i agree with your solution being better in this case.
--
Maurice Ausum

I have a query where I want to select records based on a name in the
"Assigned To" field. The user selects a name from a list on a criteria form
and the query takes the name and selects records with that name in that
field. This part is easy.

However, if the user does not select a name on the criteria form, I want the
query to return ALL records.

I don't know how to choose between these two possibilities. I can set up the
query to run one or the other, but I don't know how to conditionally choose
between them.

Does anybody know how to do this?

Pete and Maurice suggested using LIKE - and this will work, but with some
reservations. For instance, if the name selected is SMITH, you will see
records for SMITH, SMITHSON, SMITHERS, and so on if you're searching a text
field. Not what you want!

An alternative is to use

[namefield] = [Forms]![NameOfForm]![NameOfControl] OR
[Forms]![NameOfForm]![NameOfControl] IS NULL

John W. Vinson [MVP]

 

[David Portwood]

An alternative is to use

[namefield] = [Forms]![NameOfForm]![NameOfControl] OR
[Forms]![NameOfForm]![NameOfControl] IS NULL

I'll try it. Thanks to all.

 

[Pete]

The question was how do I make it print all records as the if the user
doesn't select anything so smith vs smithers isn't a consideration as he
wanted all records, But what the heck they both work.

An alternative is to use

[namefield] = [Forms]![NameOfForm]![NameOfControl] OR
[Forms]![NameOfForm]![NameOfControl] IS NULL

I'll try it. Thanks to all.

 

[John W. Vinson]

The question was how do I make it print all records as the if the user
doesn't select anything so smith vs smithers isn't a consideration as he
wanted all records, But what the heck they both work.

Well... yes, it works to print all records; but if they're searching a text
field it prints incorrect records (SMITHERS and SMITHSON). Often though the
searched field will actually be a numeric ID - and that can get incorrect
records too, much more confusingly. If the user is searching for PersonID 122
and gets hits for 122, 1220, 1221, 1222 and so on, they'll see a bunch of
apparently unrelated records!

John W. Vinson [MVP]

 

[David Portwood]

I'm wondering if either of these strategies is going to find records where
the "AssignedTo" field is null. That is also a possibility, meaning the item
has not yet been assigned to an analyst. Well, I'll find out tomorrow.

 

[John W. Vinson]

I'm wondering if either of these strategies is going to find records where
the "AssignedTo" field is null.

The "OR [forms]!..." criterion totally ignores what is (or isn't) in the table
field, so yes, it will find such records. The wildcard will not.

John W. Vinson [MVP]

 

[David Portwood]

I actually have four possible criteria for the AssignedTo field on my
Criteria form which is set up as an option group. If AssignedTo is:

Null, I want all records including those with null values;
Name, I want all records with that name in AssignedTo field;
No Assignments, All records with Null value in AssignedTo field;
All Assignments, All records with non-null values in AssignedTo field.

I had been thinking in terms of deciphering which of the four criteria I
want from the criteria form, then writing a value or string into the query
(from within an IIF in the query) to retrieve the associated records. I'm
beginning to realize that may not be the correct strategy. Or is it? I'm
stumped.

I would appreciate any help with this.

 

[John W. Vinson]

I actually have four possible criteria for the AssignedTo field on my
Criteria form which is set up as an option group. If AssignedTo is:

Null, I want all records including those with null values;
Name, I want all records with that name in AssignedTo field;
No Assignments, All records with Null value in AssignedTo field;
All Assignments, All records with non-null values in AssignedTo field.

I had been thinking in terms of deciphering which of the four criteria I
want from the criteria form, then writing a value or string into the query
(from within an IIF in the query) to retrieve the associated records. I'm
beginning to realize that may not be the correct strategy. Or is it? I'm
stumped.

It's probably not the best design but it can be made to work.
Let's assume that the option group has three controls, with 1 being Name, 2 No
Assignments, and 3 All Assignments, and that it can be left null (it should
probably be an unbound control).

Try a criterion of

WHERE
([Forms]![Criteria]![AssignedTo] IS NULL)
OR
([Forms]![Criteria]![AssignedTo] = 1
AND [Namefield] = [Forms]![Criteria]![txtName])
OR
([Forms]![Criteria]![AssignedTo] = 2
AND [Namefield] IS NULL)
OR
([Forms]![Criteria]![AssignedTo] = 3
AND [Namefield] IS NOT NULL)


John W. Vinson [MVP]

 

[David Portwood]

Can an option group be Null? By the way, there are three more fields, two of
them Date fields which can be Null, a single Date, or a range of Dates. I've
decided on a brute force approach.

I am going into the SQL statement and changing the Where clause in the
Select statement. When the user clicks OK on the Criteria form, the _Click()
event analyzes the user's responses and constructs a Where clause within the
Select statement. In the Report_Open() method, I assign the new Select
statement to the RecordSource property of the Report.

So instead of being based on a predefined query with flexible criteria, as
was my original intent, I am basing the Report on my constructed Select
statement which has hardcoded criteria based on the current set of user
responses.

Within the Where clause I have four subclauses based on the user's response
to each of the four criteria fields. I construct each subclause separately
and then chain them together, e.g.,

strAssignedToClause = " AND ((DataLog.AssignedTo)='Smith, John'"
strDateBegunClause = " AND ((DataLog.DateBegun)=#" & Me.DateBegun & "#)

And of course a subclause might also be "", which occurs when the user
leaves a criteria field blank.

When I have all my subclauses constructed, I '&' them together to form the
Where clause inside my Select statement. It's still under test, but so far
appears to work.

Thank you for your efforts on my behalf, John.

I actually have four possible criteria for the AssignedTo field on my
Criteria form which is set up as an option group. If AssignedTo is:

Null, I want all records including those with null values;
Name, I want all records with that name in AssignedTo field;
No Assignments, All records with Null value in AssignedTo field;
All Assignments, All records with non-null values in AssignedTo field.

I had been thinking in terms of deciphering which of the four criteria I
want from the criteria form, then writing a value or string into the query
(from within an IIF in the query) to retrieve the associated records. I'm
beginning to realize that may not be the correct strategy. Or is it? I'm
stumped.

It's probably not the best design but it can be made to work.
Let's assume that the option group has three controls, with 1 being Name,
2 No
Assignments, and 3 All Assignments, and that it can be left null (it
should
probably be an unbound control).

Try a criterion of

WHERE
([Forms]![Criteria]![AssignedTo] IS NULL)
OR
([Forms]![Criteria]![AssignedTo] = 1
AND [Namefield] = [Forms]![Criteria]![txtName])
OR
([Forms]![Criteria]![AssignedTo] = 2
AND [Namefield] IS NULL)
OR
([Forms]![Criteria]![AssignedTo] = 3
AND [Namefield] IS NOT NULL)


John W. Vinson [MVP]

 

[John W. Vinson]

Within the Where clause I have four subclauses based on the user's response
to each of the four criteria fields. I construct each subclause separately
and then chain them together, e.g.,

strAssignedToClause = " AND ((DataLog.AssignedTo)='Smith, John'"
strDateBegunClause = " AND ((DataLog.DateBegun)=#" & Me.DateBegun & "#)

And of course a subclause might also be "", which occurs when the user
leaves a criteria field blank.

When I have all my subclauses constructed, I '&' them together to form the
Where clause inside my Select statement. It's still under test, but so far
appears to work.

That's certainly a very good way to build a complex and variable query.

One trick is to start the WHERE clause with

strSQL = "SELECT blah blah blah FROM blah blah blah"
strSQL = strSQL & " WHERE TRUE"

This will give you something to hang the first AND onto without introducing
any order dependency in the clauses.

One "gotcha" - your Assigned To criterion will fail if the project should be
assigned to someone named O'Niel (since the apostrophe in the name will
terminate the string). I'd use " as the string delimiter - you can put

Const QUOTE As String = """"

and use

strAssignedToClause = " AND DataLog.AssignedTo = " & QUOTE & Me!cboAssignedTo
& QUOTE


John W. Vinson [MVP]

 

[David Portwood]

Good catch. We actually do have someone named O'Neil here.