Re: My parameter queries fail, whereas my select queries work fine

[Jeff Boyce]

When you tell a query to find records that match criterion1 OR criterion2 OR
... (or "Between criterion1 And criterion2"), Access uses the underlying
criteria.

When you tell a parameter query to find records that match the literal
string "criterion1 OR criterion2 OR ...", NONE of your records have that
string in the field, so you get no rows.

Or have I misunderstood what you are doing...?

Regards

Jeff Boyce
<Access MVP>

"PFMay" <PF...@discussions.microsoft.com> wrote in message
news:C78F03AA-A947-48BC...@microsoft.com...
> I'm trying to make a relatively simple parmeter query using record
> numbers.
> All but the simplest such queries (ie, a simple record number) return one
> empty line where the records should be. Even a simple OR statement
> fails,
> that is, when I type the Or statement into the parameter box when
> requested
> I get a single blank line instead of two records.
>
> The OR statement works fine when pluggged directly into the criteria line
> of
> the select query.
>
> I get the same results when I use, say, Between 25 And 30 as the
> expression. It works fine as the crieria for a select query, but reurns
> the
> single blank line whenever I substiteut a phrase such as "[enter record
> number] for the criteria and then paste or type the identical expression
> into the parameter box when I run the query.
>
> Can any one tell me what I'm doing wrong or if this a known bug in the
> program. I'm using Access 2003 with SP1 installed

[John Spencer (MVP)]

Parameter queries supply the value to be checked, they don't (can't) supply the
operator.

Between, Or, < , = etc. are operators.

If you wanted to use between with parameters you would need something like the
following in the criteria

Between [Enter Start Value] And [Enter End Value]

[pere...@jetemail.net]

John Spencer (MVP) wrote:
> Parameter queries supply the value to be checked, they don't (can't) supply the
> operator.
>
> Between, Or, < , = etc. are operators.

They can can if you code it:

CREATE TABLE Test
(data_col INTEGER NOT NULL);

INSERT INTO Test VALUES (1);

INSERT INTO Test VALUES (3);

INSERT INTO Test VALUES (5);

CREATE PROCEDURE Proc1 (
:value INTEGER,
:operator_code INTEGER
) AS
SELECT * FROM Test WHERE SWITCH(
:operator_code = 1, IIF(data_col = :value, 1, 0),
:operator_code = 2, IIF(data_col < :value, 1, 0),
:operator_code = 3, IIF(data_col > :value, 1, 0),
:operator_code = 4, IIF(data_col <= :value, 1, 0),
:operator_code = 5, IIF(data_col >= :value, 1, 0),
:operator_code = 6, IIF(data_col <> :value, 1, 0),
TRUE, 0) = 1;

To test:

EXECUTE 3, 1;
-- data_col equals 3

EXECUTE 3, 2;
-- data_col is less than 3

EXECUTE 3, 3;
-- data_col is greater than 3

etc etc

[pere...@jetemail.net]

pere...@jetemail.net wrote:
> John Spencer (MVP) wrote:
> > Parameter queries supply the value to be checked, they don't (can't) supply the
> > operator.
>

> They can can if you code it

Typo!

Parameter queries can be supplied the operator if you encode the
operator.