Using Unbound Form to Enter Query Criteria

C

cainentm

Using Access2000, I am trying to use an unbound form to specify the criteria
for my query. In the unbound form, I have multiple fields, where any of them
may be filled in to further filter the results. In cases where the field is
left blank, it should be assumed that the results should not filter based on
that criteria. For example, if my table has Department, Part#, Category,
Defect, and Type - I may want to sort all records in a specific Department,
without regards to the other fields. However, I may want to locate records
that are from a specific Department, for a specific Category, and a specific
Type. To enter the criteria for the query, I set the criteria field to look
at the combo box on my unbound form. But if any of the fields is left blank,
I get no records returned. Can anyone help with how to leave criteria fields
blank on the form?
 
O

Ofer

Try this SQL

Select * From MyTable Where
Field1 Like Nz(Forms![FormName]![Field1Name],"*") And
Field2 Like Nz(Forms![FormName]![Field2Name],"*") And
Field3 Like Nz(Forms![FormName]![Field2Name],"*")
 
K

Ken Sheridan

Test each control for Null, e.g.

SELECT *
FROM MyTable
WHERE
(Field1 = Forms!MyForm!txtFirst OR Forms!MyForm!txtFirst IS NULL)
AND (Field2 = Forms!MyForm!txtSecond OR Forms!MyForm!txtSecond IS NULL)
AND (Field3 = Forms!MyForm!txtThird OR Forms!MyForm!txtThird IS NULL);

If a control is Null then the expression within each pair of parentheses
will evaluate to True for every row in the table so a row will be returned if
the other parenthesised expressions also evaluate to True for that row,
either by virtue of having a matching value if the control is not Null, or by
virtue of the control being Null.
 
P

peregenem

Ofer said:
Select * From MyTable Where
Field1 Like Nz(Forms![FormName]![Field1Name],"*") And
Field2 Like Nz(Forms![FormName]![Field2Name],"*") And
Field3 Like Nz(Forms![FormName]![Field2Name],"*")
Click to expand...

Simplified and non-Access-specific (i.e. avoids NZ() and avoids
specifying wildcard character):

SELECT *
FROM MyTable
WHERE Field1 = IIF(param1 IS NULL, Field1, param1)
AND Field2 = IIF(param2 IS NULL, Field2, param2)
AND Field3 = IIF(param3 IS NULL, Field3, param3)
 
Top