Getting a query to use a number of values as criteria (A OR B OR C etc) which are stored in another table.

[Regnab]

This might be a little complicated to explain. I have a table
(Paddocks) with 4 property codes as fields. In each of these fields is
a list of paddocks (varying number). My code at the moment runs this
query 4 times (for each property), and I need the parameters of the
query to change depending on which property is being checked. The
reason I don't just type the properties in as criteria is because I
want the user to be able to add paddocks to the table and for them to
be automatically included in the query next time it is run. Any ideas
much appreciated...

Cheers

Reg

[pietlinden]

Not sure I follow... but you could do something like specify a table
and a field and return a list of delimited values and maybe pass that
to the query as a criterion... Would help if you gave an example of
criteria and query. hard to figure it out from what you've given.

[Regnab]

This is the SQL of the query...

SELECT Herd_Animals.Owner, Herd_Animals.Animal_Tag,
Herd_Animals.PurchaseDate, Herd_Animals.Group2_Code,
Herd_Animals.Grade, Herd_Animals.Sex_Code, Herd_Animals.Location_Code
INTO QTable3
FROM Herd_Animals
WHERE (((Herd_Animals.PurchaseDate)>=[Forms]![Form1]![TxtStartDate] And
(Herd_Animals.PurchaseDate)<=[Forms]![Form1]![TxtEndDate]) AND
((Herd_Animals.Location_Code)=[PropertyExternalMovements]![DGS]));

It's that last bit - the table is PropertyExternalMovements, DGS is the
property code. I want the query to sort and return only those
Location-Code(s) which are found in the PropertyExternalMovements table
under "DGS". By the way, DGS is from "Herd_Animals.Owner" as well - if
it could reference to "Herd_Animals.Owner" field, that'd be ideal. Does
that help explain? And by the way, thanks for your help with this and
the last question.

[jimfortune]

Regnab wrote:

This is the SQL of the query...

SELECT Herd_Animals.Owner, Herd_Animals.Animal_Tag,
Herd_Animals.PurchaseDate, Herd_Animals.Group2_Code,
Herd_Animals.Grade, Herd_Animals.Sex_Code, Herd_Animals.Location_Code
INTO QTable3
FROM Herd_Animals
WHERE (((Herd_Animals.PurchaseDate)>=[Forms]![Form1]![TxtStartDate] And
(Herd_Animals.PurchaseDate)<=[Forms]![Form1]![TxtEndDate]) AND
((Herd_Animals.Location_Code)=[PropertyExternalMovements]![DGS]));

It's that last bit - the table is PropertyExternalMovements, DGS is the
property code. I want the query to sort and return only those
Location-Code(s) which are found in the PropertyExternalMovements table
under "DGS". By the way, DGS is from "Herd_Animals.Owner" as well - if
it could reference to "Herd_Animals.Owner" field, that'd be ideal. Does
that help explain? And by the way, thanks for your help with this and
the last question.

Piet has probably already posted the answer to this by now, but perhaps
try ending with:

AND (Herd_Animals.Location_Code) IN (SELECT DISTINCT DGS FROM
PropertyExternalMovements WHERE DGS IS NOT NULL);

instead of:

AND ((Herd_Animals.Location_Code)=[PropertyExternalMovements]![DGS]));

James A. Fortune

Since the [SMTP/ESMTP] message headers are given to the server twice
(once in the envelope and again in the message headers), a loophole for
spammers exists. One would hope that a modern server implementation
would not only validate the sender in the envelope, but also reject
messages where the sender as given in the envelope fails to match the
sender's address in the TO or Reply-To message headers. --- Dan Wood,
Programming Internet Email, O'Reilly 1999, ISBN 1-56592-479-7

[Regnab]

BRILLANT. Spot on. Cheers mate....