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));
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));