A little difficult to GUESS which fields are your yes/no fields.
Are you applying criteria against several fields and want to return the
record if any one of the fields is true. If so, you need to OR the
criteria by putting the true on separate criteria lines.
SELECT [tbl retreat 2008].ID, [tbl retreat 2008].[Last Name]
, [tbl retreat 2008].[Other Name], [tbl strat yrs].[2009 invite]
, [tbl strat yrs].[2009 All Day], [tbl strat yrs].[2009 AM]
, [tbl strat yrs].[2009 PM], [tbl strat yrs].[2009 Attending]
, [tbl strat yrs].[2009 notes]
FROM [tbl retreat 2008] INNER JOIN [tbl strat yrs]
ON [tbl retreat 2008].ID = [tbl strat yrs].ID
WHERE [2009 Invite] = True
OR [2009 AM] = True
OR [2009 PM] = True
OR [2009 Attending] = True
AND if you are applying criteria against the fields in tbl Strat yrs you
might as well change the LEFT Join to an INNER Join. Applying criteria
against fields in tbl Strat Yrs will negate the affects of the LEFT JOIN
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
Thanks for responding!
This is what I have:
SELECT [tbl retreat 2008].ID, [tbl retreat 2008].[Last Name], [tbl retreat
2008].[Other Name], [tbl strat yrs].[2009 invite], [tbl strat yrs].[2009 All
Day], [tbl strat yrs].[2009 AM], [tbl strat yrs].[2009 PM], [tbl strat
yrs].[2009 Attending], [tbl strat yrs].[2009 notes]
FROM [tbl retreat 2008] LEFT JOIN [tbl strat yrs] ON [tbl retreat 2008].ID =
[tbl strat yrs].ID;
Thanks again!!!
John Spencer (MVP) said:
What type of field are you doing this against? It might be something other
than a boolean (yes/no) field since you can filter for is null or you could be
using an outer join (left join or right join )
Can you post the SQL of your query (Hint: View: SQL on the menu)? If you are
using an outer join, then it is possible to get null values returned for the
boolean field and it is possible to have various problems because of that.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Patty wrote:
Thanks Steve!!
I would like to know why the filter will not work. It works for yes, but
not no. Any thoughts?
:
Select *
From [YourTableName]
Where Not [YourFieldName]
Should return all of the 'No' values.
Post your SQL in the Queries forum for mo'betta service.
--
Steve Clark, Former Access MVP
SharePoint pwned
Washington, DC
:
I created 2 tables and created a relationship using ID#, 1 to many. I used
both tables in a query. I can filter in the y/n box for yes, but not for no.
I can also filter y/n in the other columns as long as the first y/n column
has been marked yes. When I filter no, it brings up only those marked yes in
the first column. I can filter using "is null" in the design view.
Any suggestions?
Using Access 2007.
Thanks!!
