PARAMETER QUERY

[Guest]

I am using a parameter query to retrieve records for a report. I type [Enter
Group Code] in the criteria row. I type in 115 when prompted and my records
are retrieved and displayed. But I want records for Group 115 OR 125 when
prompted. How do I phrase the expression to find multiple values in the same
field using OR operator? 115 OR 125 retrieves no records. Any ideas...

 

[John Spencer]

That is a limitation of a parameter query.

You can use
-- two parameters or
-- VBA code to build the where clause (or the entire query)
or other solutions.

PERHAPS something like the following will work for you, although this cannot
use any indexes that may be available and will be slow with large numbers of
records.

WHERE Instr(1,[Enter Group Code],Group) > 0

In the query grid
Field: Expr1: Instr([Enter Group Code],Group)
Criteria: > 0

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

 

[Guest]

John,

Thank you so much. I simply separate by a comma my deisred groups when
prompted and it retrieves records instantly. I ran it against 100K records
and NO lag whatsoever. And all this time I have been running the same query
with different parameters each time. Thank you again.

That is a limitation of a parameter query.

You can use
-- two parameters or
-- VBA code to build the where clause (or the entire query)
or other solutions.

PERHAPS something like the following will work for you, although this cannot
use any indexes that may be available and will be slow with large numbers of
records.

WHERE Instr(1,[Enter Group Code],Group) > 0

In the query grid
Field: Expr1: Instr([Enter Group Code],Group)
Criteria: > 0

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

I am using a parameter query to retrieve records for a report. I type
[Enter
Group Code] in the criteria row. I type in 115 when prompted and my
records
are retrieved and displayed. But I want records for Group 115 OR 125 when
prompted. How do I phrase the expression to find multiple values in the
same
field using OR operator? 115 OR 125 retrieves no records. Any ideas...

 

[Guest]

John,

Is it possible to use wildcards with this function to search for text?

I tried Like "*" & Instr([Enter Competitive Line],[Competitive Line]) -
Bad syntax message. I tried Like "*" & Instr([Enter Competitive
Line],[Competitive Line])&"*" Bad syntax again Any help would be
appreciated.

Thanks

That is a limitation of a parameter query.

You can use
-- two parameters or
-- VBA code to build the where clause (or the entire query)
or other solutions.

PERHAPS something like the following will work for you, although this cannot
use any indexes that may be available and will be slow with large numbers of
records.

WHERE Instr(1,[Enter Group Code],Group) > 0

In the query grid
Field: Expr1: Instr([Enter Group Code],Group)
Criteria: > 0

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

I am using a parameter query to retrieve records for a report. I type
[Enter
Group Code] in the criteria row. I type in 115 when prompted and my
records
are retrieved and displayed. But I want records for Group 115 OR 125 when
prompted. How do I phrase the expression to find multiple values in the
same
field using OR operator? 115 OR 125 retrieves no records. Any ideas...

 

[John Spencer]

No, use of wildcards is not possible.

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

John,

Is it possible to use wildcards with this function to search for text?

I tried Like "*" & Instr([Enter Competitive Line],[Competitive Line]) -
Bad syntax message. I tried Like "*" & Instr([Enter Competitive
Line],[Competitive Line])&"*" Bad syntax again Any help would be
appreciated.

Thanks

That is a limitation of a parameter query.

You can use
-- two parameters or
-- VBA code to build the where clause (or the entire query)
or other solutions.

PERHAPS something like the following will work for you, although this
cannot
use any indexes that may be available and will be slow with large numbers
of
records.

WHERE Instr(1,[Enter Group Code],Group) > 0

In the query grid
Field: Expr1: Instr([Enter Group Code],Group)
Criteria: > 0

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

I am using a parameter query to retrieve records for a report. I type
[Enter
Group Code] in the criteria row. I type in 115 when prompted and my
records
are retrieved and displayed. But I want records for Group 115 OR 125
when
prompted. How do I phrase the expression to find multiple values in
the
same
field using OR operator? 115 OR 125 retrieves no records. Any ideas...