Hi,
Here is the sql statement.
SELECT tblDepts.DeptID, tblDepts.[Department Name], tblUse.Status,
tblUse.Deliverable
FROM tblDepts LEFT JOIN tblUse ON tblDepts.DeptID = tblUse.DeptID
WHERE (((tblDepts.[Department Name])=[Forms]![frmSelect]![txtName]) AND
((tblUse.Deliverable)=[Forms]![frmSelect]![cboDeliverable])) OR
(((([tblDepts].[Department Name]) Like [Forms]![frmSelect]![txtName]) Is
Null));
That's not at all what I suggested. A LIKE operator crept in from somewhere...
try
SELECT tblDepts.DeptID, tblDepts.[Department Name], tblUse.Status,
tblUse.Deliverable
FROM tblDepts LEFT JOIN tblUse ON tblDepts.DeptID = tblUse.DeptID
WHERE (tblDepts.[Department Name]=[Forms]![frmSelect]![txtName]
OR [Forms]![frmSelect]![txtName] IS NULL)
AND
(tblUse.Deliverable=[Forms]![frmSelect]![cboDeliverable]
OR [Forms]![frmSelect]![cboDeliverable] IS NULL);
What I'm doing here is using a parenthesized clause for each field, consisting
of two subclauses: one comparing the field to the form control, and the other
comparing the form control to NULL.
Right now, if I type the dept name and select the deliverable on the form,
it gives me the correct record. But if I leave the dept name field blan and
select a deliverable, it returns all records.
It's tricky getting the parenthesis nesting and the ANDs and ORs correct!
If I do not select a dept name and I select the deliverable, it doesn't
return any records.
I want to have a list of parameters that can be selected or ignored. If one
is ignored it should run the query for the selected parameters.
If the list of parameters exceeds three or four, this technique is going to
get excessively complicated. At the very least you should construct the query
in the SQL window and avoid EVER going into Design view; doing so will cause
Access to completely reshuffle the query with a whole lot of calculated fields
and convoluted logic.
An alternative approach is to have multiple criteria textboxes on a form and a
"Search" button; the button would poll through the controls and actually build
a SQL string from scratch, ignoring NULL controls and concatenating a clause
like
AND [Department Name] = [Forms]![frmSelect]![txtName]
when it finds that txtName contains data.
When the SQL string is complete you would use it as the Recordsource of a form
or report.
THere are "end effects" - the first non-null criterion starts with AND, which
is bad grammar; one trick is to start with a string like
SELECT tblDepts.DeptID, tblDepts.[Department Name], tblUse.Status,
tblUse.Deliverable
FROM tblDepts LEFT JOIN tblUse ON tblDepts.DeptID = tblUse.DeptID
WHERE 1 = 1
which will retrieve all records; appending one or more AND clauses to this
will restrict the retrieval.
