Parameter Query with Variable Criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that lists the number of truck drivers for each company, I
want to set up a parameter query that I can say show me all the company that
have
200 drivers and then the next time I run the query I want to see <30 drivers. So every time my parameter may be different.
Click to expand...

How do I set that up? Is is not always > or < sometimes it is >= or <>
 
Create a form with two text boxes: Forms!frmSelect!txtMin and
Forms!frmSelect!txtMax.

Then set your criteria to:
Between Forms!frmSelect!txtMin and Forms!frmSelect!txtMax
This should allow you to filter however you want.
 
I have a query that lists the number of truck drivers for each
company, I want to set up a parameter query that I can say show me all
the company that have

How do I set that up? Is is not always > or < sometimes it is >= or
<>
Click to expand...

A primitive solution? In your query:

Criterium for your field ? TruckdrNo: >=[Low?]

Add the same field to the query
Criterium: <=[High?]
Untick Show for the latter field.

You yourself then have to think of the correct low and high values to meet
your > or < or >=

I couldn't think of a solution for <> in the above.
Maybe add the field again to the query and
Criterium: <>[Not?]
To nullify this criterium always give a value (far) above the largest
number in your table.

Yes, that works! The only thing with this solution is that you always have
to enter 3 criteria. ;-)))
 
Erika said:
I have a query that lists the number of truck drivers for each company, I
want to set up a parameter query that I can say show me all the company that
have

How do I set that up? Is is not always > or < sometimes it is >= or <>
Click to expand...

Take a look at the following WHERE clause. Sorry, I can't recreate your
front end elements and you may have trouble creating my proc with
parameters (sorry!) but you should be able to see how my WHERE clause
could be used in your SQL.

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
 
Back
Top