SQL: Commands to Filter a Check Box's YES/NO Values?

[warrenpage]

Hi everyone. I'm just beginning to study SQL and have not yet written an SQL command on my own yet…although I've tried. I was hoping I could get a little assistance writing a command or two. I'm using Access 2013.



My movie database has a check box for Foreign, to indicate whether the movie is foreign or not. If it is checked, it is foreign. In the table (tblMovies), the field name is Foreign, and has only YES or NO values.
I'm trying to write an SQL command to show only the records that have YES in the Foreign field. I want the query to show all the fields.
First, I created a simple query with all the fields, named qryForeign.

Then, I tried writing the following command, which didn't work:

SELECT *
FROM tblMovies
WHERE Foreign='Yes';

The error box said, "Data type mismatch in criteria expression."

I'm assuming the asterisk is the problem, but I'm not sure what to do next. Would anyone like to help me correct this problem? Am I close?

Now…to make things a little more complicated…once I solve the above problem, I would like to write a second SQL statement.

I have three subforms. One is called subgenre, and has movie genres such as Comedy, Documentary, Animation, Western, Foreign, etc.

I'd like to write an SQL statement that displays only the records that have BOTH the Foreign check box checked, AND has Foreign listed in the Genre subform.
Is that possible?

Each of the three subforms has its own junction table, since I am working with many-to-many relationships. The junction table for the Genre subform is jnctGenre.

I don't want anyone to write the SQL commands for me; instead, I'd like help writing it myself. Thank you! Warrren Page

[lfpm062010]

The Yes/No value does not need quotes.

SELECT *
FROM tblMovies
WHERE Foreign=Yes;

Have table structure and/or query that you have would help understand how to rewrite your query.

[warrenpage]

So the only mistake I made was the quotes. It worked. Thank you.

[rpeare]

just an aside you can also do

WHERE foreign = -1

yes/no fields are actually stored as -1 (yes) and 0 (no)

I usually use the numbers because they're handy when performing calculations (like sums/counts etc)

[warrenpage]

A follow-up question, please.
I want to do pretty much the same thing as the above example, only with a field in a subform. I'm having trouble doing that.

For example, I have a subform for movie directors. The junction table is jnctDirectors and the field I want to write SQL for is named DirectorLastName.

The Directors table is tblDirectors.

I successfully created a simple query that displays all the fields I want, including DirectorLastName.
Let's say I want to filter the query for movies directed by Chaplin.

My statement is:

SELECT tblMovies.*
FROM tblMovies
WHERE tblMovies.DirectorLastName='Chaplin'

I get the Enter Parameter Value box, and don't know if I should fill anything in or not.

I then tried the following:

SELECT tblMovies.*
FROM tblMovies
WHERE tblDirectors.DirectorLastName='Chaplin'

And I get the Parameter box as well.
So, I’m not quite sure how to write this. Do I need to include the DirectorID field that I included in the junction table? How close am I to getting this right? Thanks again. Warren Page

[June7]

tblMovies should not have the director's name, it should only have the director's ID.

Are you setting Lookups on fields in table? Advise not to do that. http://www.bing.com/search?q=evils+l...&sp=1&qs=HS&sk=

So even if you have the field named DirectorLastName in tblMovies, the value should actually be the ID. Therefore a search on 'Chaplin' would fail. However, because you get a popup, that indicates there is no such field DirectorLastName in tblMovies.

The second query fails because tblDirectors is not included in the query by joining to tblMovies.

[warrenpage]

I've never worked with lookups.

I do not understand why I need the ID. How do I perform a text search with a numeric value? I mean, if a Movie ID or Director ID is, say, 345, how do I search for "Chaplin" via SQL with a number?

Are there any SQL commands that should be in the statement that I haven't included?

[June7]

Build a query that joins tblMovies with tblDirectors then all related info is available. Use the Access query builder to help construct then switch to SQL View to see the sql statement.

[warrenpage]

I think I got it.

SELECT tblMovies.*, jnctDirector.DirectorID, tblDirectors.DirectorLastName
FROM tblMovies INNER JOIN (tblDirectors INNER JOIN jnctDirector ON tblDirectors.DirectorID = jnctDirector.DirectorID) ON tblMovies.MovieID = jnctDirector.MovieID
WHERE DirectorLastName='Chaplin';

I was having trouble with this, until I realized I needed to remove the semicolor from the end of the second line.