I'm trying to create a query that uses the selection of a single combobox as part of the WHERE clause.
The query:
SELECT Database.Contact_ID, Database.[Full Name], Database.[Job Title],
Institution.Institution, Database.Email, Database.[Email 2], Database.[Work
phone], Database.[Work phone 2], Database.Mobile, Database.Notes, Database.
[Date created], [Keyword Junction].Keywords, Database.[Record Type]
FROM Institution INNER JOIN (Keywords INNER JOIN ([Database] INNER JOIN
[Keyword Junction] ON Database.[Contact_ID] = [Keyword Junction].Contact_ID)
ON Keywords.Keyword_ID = [Keyword Junction].Keywords.Value) ON
Institution.ID = Database.InstitutionLookup
WHERE ((Keywords.Keyword)=[Forms]![Keyword Search Mk 2]![SelectKeyword]);
The 'Keyword Search Mk 2' form is a simple pop out form with the combobox that allows the user to select the desired keyword. I can select the keyword in the form but when I run the search the results are empty.
The previous WHERE clause was:
WHERE ((Keywords.[Keyword]) Like "*" & [Forms]![Keyword Search Mk 2]!
[SelectKeyword] & "*");
This had the same issue. I've also tried specifying the column number e.g.
WHERE ((Keywords.[Keyword]) Like "*" & [Forms]![Keyword Search Mk 2]!
[SelectKeyword].[Columns](1) & "*");
Again with no luck.
Appreciate any suggestions, thanks.
Per the conversation in the comments, when a record is selected, the value of the ComboBox control will be equal to value of the field in the Row Source which corresponds to the Bound Column number.
For example, if your Row Source dataset contains three fields and the Bound Column is set to 2, the value of the second field in the dataset will be returned by the expression [Forms]![YourFormName]![YourComboBox].