Search multiple values in multiple tables

[andyhall]

I have four tables (Contacts, LanguagesExcellent, LanguagesGood, and LanguagesFair) with a key of ContactID. I'm trying to write the SQL that will allow users to enter no, one, or several options for each of the three language proficiencies (i.e., Excellent, Good, and Fair) and return the contacts for the matching criteria. I've got it to work if I enter nothing or just one value but I'm stumped beyond that. Specifically, if I enter more than one value, the query only looks at the last value in the list. How can I get it to grab all of the values? Secondly, once it does return multiple values, what is the code for iterating through each instance...possibly for one, two, or all three of the language proficiences? The code I have so far is below. Also, I have the users querying from an "input form" and, specifically, using three subforms for entering the three different language proficiencies.

Thanks for any help!!!

SELECT tblContacts.ContactID, tblContacts.FirstName, tblContacts.LastName, tblContacts.PositionTitle, tblLanguagesExcellent.LanguageExcellent, tblLanguagesGood.LanguageGood, tblLanguagesFair.LanguageFair
FROM ((tblContacts INNER JOIN tblLanguagesExcellent ON tblContacts.ContactID = tblLanguagesExcellent.ContactID) INNER JOIN tblLanguagesFair ON tblContacts.ContactID = tblLanguagesFair.ContactID) INNER JOIN tblLanguagesGood ON tblContacts.ContactID = tblLanguagesGood.ContactID
WHERE (((([tblContacts].[GenderExperience])=[Forms]![frmQueryByForm]![GenderExperience] Or [Forms]![frmQueryByForm]![GenderExperience] Is Null)<>False) AND ((([tblLanguagesExcellent].[LanguageExcellent])=[Forms]![frmQueryByForm]![sfrLEInput]![LanguageExcellent] Or [Forms]![frmQueryByForm]![sfrLEInput]![LanguageExcellent] Is Null)<>False) AND ((([tblLanguagesGood].[LanguageGood])=[Forms]![frmQueryByForm]![sfrLGQry]![LanguageGood] Or [Forms]![frmQueryByForm]![sfrLGQry]![LanguageGood] Is Null)<>False) AND ((([tblLanguagesFair].[LanguageFair])=[Forms]![frmQueryByForm]![sfrLFQry]![LanguageFair] Or [Forms]![frmQueryByForm]![sfrLFQry]![LanguageFair] Is Null)<>False));

 

[wemeier]

I don't know what datatype LanguageExcellent is, but if it's text you could change

((([tblLanguagesExcellent].[LanguageExcellent])=[Forms]![frmQueryByForm]![sfrLEInput]![LanguageExcellent] Or [Forms]![frmQueryByForm]![sfrLEInput]![LanguageExcellent] Is Null)<>False

to

[tblLanguagesExcellent].[LanguageExcellent] Like [Forms]![frmQueryByForm]![sfrLEInput]![LanguageExcellent]

When the field on the form is filled in, you'll have to match on the contents, but when the field is empty you'll match on all records that DON'T have a match.