Selecting Criteria from Combo Box

[Guest]

I have two combo boxes on a reports menu for Category and SubCat. A query
runs and displays data according to what the user chose in these fields. This
works ok.

If the fields are left blank this also works ok and displays all closed
calls for the specified date range regardless of categories/subcats.

If the user chooses a Category though but leaves the SubCat blank I want the
query to return all calls for that category regardless of the SubCat and it
is this part which doesn't work. The query returns no data. Anyone any ideas?
The SQL is below:

SELECT [Call Log].*, [First Name] & " " & [surname] AS Caller
FROM Users INNER JOIN [Call Log] ON Users.UserID = [Call Log].UserID
WHERE ((([Call Log].Status)="closed") AND (([Call Log].DateClosed) Between
[forms]![Reports Menu]![StartDate] And [forms]![Reports Menu]![EndDate]) AND
(([Call Log].Category)=[forms]![reports menu]![category]) AND (([Call
Log].SubCat)=[forms]![reports menu]![subcat]))
OR
((([Call Log].Status)="closed") AND (([Call Log].DateClosed) Between
[forms]![Reports Menu]![StartDate] And [forms]![Reports Menu]![EndDate]) AND
(([Call Log].Category)=[forms]![reports menu]![category]) AND
(([forms]![reports menu]![subcat]) Is Null))
OR
((([Call Log].Status)="closed") AND (([Call Log].DateClosed) Between
[forms]![Reports Menu]![StartDate] And [forms]![Reports Menu]![EndDate]) AND
(([forms]![reports menu]![subcat]) Is Null) AND (([forms]![reports
menu]![category]) Is Null));

Thanks in advance

 

[Guest]

It sounds like you are okay if the user takes "all or nothing" but not if
they take some. You may need to have a procedure build the SQL based on what
the user does or doesn't select. Alternatively, you can have two queries and
decide which to call based on what they do or don't select.