join table query problem

[Alexandre Saillant]

Hello,

Got a little problem with a query. I have 2 tables that are linked.
The table one is used has a filter for a complex multiple table query
that start with table 2. basicaly I insert the value I want in table
1. and the table 2 will select the matching data. After that the
rest of the queries will work with only the right data. Here is a
sample to help you understand:

Table 1: (contains my categories)

Model Year
Sunfire 2004
Sunfire 2005
Cavalier 2004
Cavalier 2005

Table 2: (contains detailed data)

Model Vin Year Transmission Color etc.
Sunfire 1jb2d... 2004 Automatic blue ...
Sunfire 1jb3d... 2004 Automatic Red ...
Sunfire 1jb4d... 2005 Automatic blue ...
Sunfire 1jb5d... 2005 Automatic Green ...
Cavalier 1jb6d... 2004 Automatic blue ...
Cavalier 1jb7d... 2004 Automatic Silver ...
Cavalier 1jb8d... 2004 Automatic blue ...

In my join query I liked Model and Year between them. So if I select
Sunfier and 2004. I sould have a query that list only the sunfire of
2004.
THE PROBLEM is when I want 2004 AND 2005!!!
If i was writing the condition in the query I would simply write "2004
and 2005" in the CRITERIA section. However, because I MUST write the
criteria in the table 1 field, I dont know what to put. I wont take
"2004 and 2005" because it will look for that excat data in the year
field and will returne nothing. the "like 200*" will not work either!
Is there anything I can do for this?

 

[John Vinson]

If i was writing the condition in the query I would simply write "2004
and 2005" in the CRITERIA section. However, because I MUST write the
criteria in the table 1 field, I dont know what to put.

2004 AND 2005 would NEVER work as a criterion (since a value can be
either 2004, or 2005; it cannot be both 2004 AND 2005)!

Use either [Year] = 2004 OR [Year] = 2005 (or, equivalently, put 2004
on one line of the query grid and 2005 on the next); or use a
criterion of

IN (2004, 2005)

 

[Alexandre Saillant]

Yes, you are right, I meant OR not AND. However, my question is not
there. it's how do you reproduce a command that I would TYPE in query
creteria into an table that would be liked in the query. basicaly the
criteria will be a value in a field from a linked table.

FROM MasterList INNER JOIN OptionTable ON MasterList.Year =
OptionTable.Year;




you see? OptionTable.Year is either "4" or "5"! in a TABLE FIELD, if
I type "in(4,5)" my query will look for "in(4,5)" as a text field and
will of course find neither 4 or 5 and will return nothing instead of
returning all results with year 4 or 5.

It's easy to do manaly but I am trying to control my query from the
internet throug a DAP that will update a Criteria table. THis is
linked to a bunch of queres and so one. It works greate when I only
want one type of info, but if I want to use multiples criteria like
this, 4 or 5, It does not work!

How Can I fix this?

If i was writing the condition in the query I would simply write "2004
and 2005" in the CRITERIA section. However, because I MUST write the
criteria in the table 1 field, I dont know what to put.

2004 AND 2005 would NEVER work as a criterion (since a value can be
either 2004, or 2005; it cannot be both 2004 AND 2005)!

Use either [Year] = 2004 OR [Year] = 2005 (or, equivalently, put 2004
on one line of the query grid and 2005 on the next); or use a
criterion of

IN (2004, 2005)

 

[John Vinson]

you see? OptionTable.Year is either "4" or "5"! in a TABLE FIELD, if
I type "in(4,5)" my query will look for "in(4,5)" as a text field and
will of course find neither 4 or 5 and will return nothing instead of
returning all results with year 4 or 5.

It's easy to do manaly but I am trying to control my query from the
internet throug a DAP that will update a Criteria table. THis is
linked to a bunch of queres and so one. It works greate when I only
want one type of info, but if I want to use multiples criteria like
this, 4 or 5, It does not work!

How Can I fix this?

With a Subquery:

IN (SELECT OptionTable.Year FROM OptionTable WHERE <criteria>)

John W. Vinson[MVP]
(no longer chatting for now)