My parameter queries fail, whereas my select queries work fine

[Guest]

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

 

[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>

 

[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]

 

[peregenem]

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,
perator_code INTEGER
) AS
SELECT * FROM Test WHERE SWITCH(
perator_code = 1, IIF(data_col = :value, 1, 0),
perator_code = 2, IIF(data_col < :value, 1, 0),
perator_code = 3, IIF(data_col > :value, 1, 0),
perator_code = 4, IIF(data_col <= :value, 1, 0),
perator_code = 5, IIF(data_col >= :value, 1, 0),
perator_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

 

[peregenem]

They can can if you code it

Typo!

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