Shoutbox

Okay, I have a database of films.....I want a search function to be able to find any films that a given pair of actors appear in (Owen Wilson & Ben Stiller, for example) when they could be in any one of the 4 "Actor" fields.

Here's the code I have:

sql code:

---

SELECT tblFilms.ID, tblFilms.Title, tblFilms.Year, tblFilms.Actor1, tblFilms.Actor2, tblFilms.Actor3, tblFilms.Actor4
FROM tblFilms
WHERE (((tblFilms.Actor1)=[Enter the first actor who's films you would like to find] Or (tblFilms.Actor1)=[Enter the second actor who's films you would like to find])) OR (((tblFilms.Actor2)=[Enter the first actor who's films you would like to find] Or (tblFilms.Actor2)=[Enter the second actor who's films you would like to find])) OR (((tblFilms.Actor3)=[Enter the first actor who's films you would like to find] Or (tblFilms.Actor3)=[Enter the second actor who's films you would like to find])) OR (((tblFilms.Actor4)=[Enter the first actor who's films you would like to find] Or (tblFilms.Actor4)=[Enter the second actor who's films you would like to find]));

---

That's the closest I could get.....but as you'll know, that finds any film which EITHER actor was in.  My SQL knowledge isn't good enough to figure out how to get it to work properly, but I'm sure it'll be easy enough for someone who knows what they're doing!

Thanks