Filter Query Based on ComboBox Value and LIKE statement 1

[jmgibson]

Hi....I'm scratching my head on what I thought would be an easy query. I have a table that has employee names and their specific department. For example.

Mark Smith | Division 1 - Atlanta
Joe Bob | Division 1 - Boston
Henry B | Division 3 - New York

What I would like to do is to have a query return all employees in a single division REGARDLESS of their office (I know, the division and office should have been separated during DB design, but they are not).

I can run a simple query like the following that does this without issue:

SELECT DISTINCT tblEmployees.LName, tblDivisionList.DivisionNm
FROM tblDivisionList INNER JOIN tblEmployees ON tblDivisionList.DivisionID = tblEmployees.DivisionID
WHERE (((tblDivisionList.DivisionNm) Like "division 1*"));

However, what I really want to do is pull the query parameter from a combo box. I have a table with division names in it (without offices), so the query I'd like to use looks like this:

SELECT DISTINCT tblEmployees.LName, tblDivisionList.DivisionNm
FROM tblDivisionList INNER JOIN tblEmployees ON tblDivisionList.DivisionID = tblEmployees.DivisionID
WHERE (((tblDivisionList.DivisionNm) Like "[Forms]![ReportsMain]![cboDivAllOFFICES]*"));

The first query works, returning all employees in division 1, regardless of their office; however, the second query returns 0 records. I've made sure that the table with the division names (without offices) matches, so I'm not sure what's going on.

Thoughts?

 

[Golom]

By putting quotes around

[Forms]![ReportsMain]![cboDivAllOFFICES]*

You are trying to match that literal string rather than the contents of the combo box. Try something like

Like [Forms]![ReportsMain]![cboDivAllOFFICES] & "*"));

I'm assuming of course that the combo box contains something like "Division 1". If it just contains "1" for example then you will need to put a "*" character before the combo reference to enable a search anywhere within the string you are searching.