Query criteria

  • Thread starter Thread starter Jim Pockmire
  • Start date Start date
J

Jim Pockmire

I would like set criteria based on a value from a form, such that if the
value from the form is "1" a specific recordset is returned and if the value
is not "1" all records are returned. Can this be accomplished in a single
criteria cell?

e.g. I tried the following without success

=IIf(Forms!Form1!Option=1,"Ohio", Like "*")
 
Assuming your field ALWAYS has data you can use

LIKE IIf(Forms!Form1!Option=1,"Ohio","*")

Or if your field can contain nulls, you can force a value in it by appending
a zero length string and using the first option
Field: State: [TableName].[State] & ""
Criteria: LIKE IIf(Forms!Form1!Option=1,"Ohio","*")

If this is too slow, then there are other options available.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
I know the LIKE operator is slow - what are your thoughts regarding other
options?

John Spencer said:
Assuming your field ALWAYS has data you can use

LIKE IIf(Forms!Form1!Option=1,"Ohio","*")

Or if your field can contain nulls, you can force a value in it by
appending a zero length string and using the first option
Field: State: [TableName].[State] & ""
Criteria: LIKE IIf(Forms!Form1!Option=1,"Ohio","*")

If this is too slow, then there are other options available.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Jim Pockmire said:
I would like set criteria based on a value from a form, such that if the
value from the form is "1" a specific recordset is returned and if the
value is not "1" all records are returned. Can this be accomplished in a
single criteria cell?

e.g. I tried the following without success

=IIf(Forms!Form1!Option=1,"Ohio", Like "*")
Click to expand...
Click to expand...
 
I've not found LIKE to be slow if no wild cards are involved. In that case,
it appears to have the same performance as equal.

And I think the with just the "*" the performance would be just as fast as
any other method. And I've tested with leading characters folllowed by just
the asterisk and found it pretty quick there also. Like seems to use any
indexes that exist when it can do so such as when searching for "ABC*"

LIKE becomes slow when you give the SQL engine many options to check, such
as
LIKE "*ABC*"
LIKE "*[A-F]"
LIKE "[A-z][0-8]*32"

If you want to avoid using likecompletely then try the following

Field: [State]
Criteria: = IIf(Forms!Form1!Option=1,"Ohio",Null) OR Forms!Form1!Option<>1

WARNING: When you save this Access will restructure the WHERE clause of the
query.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Jim Pockmire said:
I know the LIKE operator is slow - what are your thoughts regarding other
options?

John Spencer said:
Assuming your field ALWAYS has data you can use

LIKE IIf(Forms!Form1!Option=1,"Ohio","*")

Or if your field can contain nulls, you can force a value in it by
appending a zero length string and using the first option
Field: State: [TableName].[State] & ""
Criteria: LIKE IIf(Forms!Form1!Option=1,"Ohio","*")

If this is too slow, then there are other options available.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Jim Pockmire said:
I would like set criteria based on a value from a form, such that if the
value from the form is "1" a specific recordset is returned and if the
value is not "1" all records are returned. Can this be accomplished in a
single criteria cell?

e.g. I tried the following without success

=IIf(Forms!Form1!Option=1,"Ohio", Like "*")
Click to expand...
Click to expand...
Click to expand...
 
Back
Top