Filter query based on form entries

  • Thread starter Thread starter Marco Alting
  • Start date Start date
M

Marco Alting

Hi,

I've created a form with combo-boxes to select values. And with these values
I want to run my query, to create a report. However, when I use more then 1
filter field in my query, it comes up empty. The data is in the database.
Here's my SQL for the query that is supposed to run:

SELECT [EDMS PILOT].*, * FROM [EDMS PILOT]
WHERE ((([EDMS PILOT].Portfolio) Like
[Forms]![edms_reports]![FiltPortfolio]) AND (([EDMS PILOT].Project) Like
[Forms]![edms_reports]![FiltProject]) AND (([EDMS PILOT].[Sub-Project]) Like
[Forms]![edms_reports]![FiltSubProject]) AND (([EDMS PILOT].[Document Type])
Like [Forms]![edms_reports]![FiltDocumentType]) AND (([EDMS PILOT].[Category
Discipline]) Like [Forms]![edms_reports]![FiltCategory]) AND (([EDMS
PILOT].[Key Word]) Like [Forms]![edms_reports]![FiltKeyword]));


Can anyone tell me why these multiple options will give me an empty query
result?

Thanks,
Marco
 
Marco

From your description, it isn't clear why your SQL statement uses the "Like"
qualifier. If you want to select based on the value in the combo box, you
don't need "Like". Conversely, if the value in the combo box is only
"approximate", your "Like" statement needs to include one/more appropriate
wildcard designators (in Access, use "?" for a single character, "*" for any
number of characters).

If not every combo box has had a value selected, your statement, as written,
will select ... WHERE ... VariableX = ComboBoxX = Null.

An alternate approach is to dynamically build a SQL statement "behind" the
form with the combo boxes, adding in the ones that have a value selected and
leaving out those without values selected.

Or, you could alter the statement to something along these lines: ... WHERE
.... VarX = ComboBoxX or VarX Is Null. The downside of this approach is, I
believe, that Access will have to evaluate every (multiple) combination.
Much easier to only include the valid, selected comboboxes...
 
Back
Top