Query Not Populating multiple results

[jj1]

I am using the query below


What I am trying to do is get 4 combinations



1) Department + source of Tag+ specific Date
2) Department + Specific Date
3) Source of Tag + Specific Date
4) All records


This query is meeting my 2nd and 3rd condition however if I delete the paramters for 2nd and 3rd 1st combination works fine. But I want all four conditions met in one query



SELECT [Unsafe Act Unsafe Condition].*, [Unsafe Act Unsafe Condition].Department, [Unsafe Act Unsafe Condition].Date, [Unsafe Act Unsafe Condition].[Source Of Tag], [Unsafe Act Unsafe Condition].[Close Date]
FROM [Unsafe Act Unsafe Condition]
WHERE ((([Unsafe Act Unsafe Condition].Department)=[Forms]![frmHighLevelReport]![cboDepartment]) AND (([Unsafe Act Unsafe Condition].Date) Between [Forms]![frmHighLevelReport]![cboStartDate] And [Forms]![frmHighLevelReport]![cboEndDate] Or ([Unsafe Act Unsafe Condition].Date) Between [Forms]![frmHighLevelReport]![cboStartDate] And [Forms]![frmHighLevelReport]![cboEndDate]) AND (([Unsafe Act Unsafe Condition].[Source Of Tag])=[Forms]![frmHighLevelReport]![cboSourceOfTag])) OR ((([Unsafe Act Unsafe Condition].Department)=[Forms]![frmHighLevelReport]![cboDepartment]) AND (([Unsafe Act Unsafe Condition].Date) Between [Forms]![frmHighLevelReport]![cboStartDate] And [Forms]![frmHighLevelReport]![cboEndDate] Or ([Unsafe Act Unsafe Condition].Date) Between [Forms]![frmHighLevelReport]![cboStartDate] And [Forms]![frmHighLevelReport]![cboEndDate])) OR ((([Unsafe Act Unsafe Condition].Date) Between [Forms]![frmHighLevelReport]![cboStartDate] And [Forms]![frmHighLevelReport]![cboEndDate] Or ([Unsafe Act Unsafe Condition].Date) Between [Forms]![frmHighLevelReport]![cboStartDate] And [Forms]![frmHighLevelReport]![cboEndDate]) AND (([Unsafe Act Unsafe Condition].[Source Of Tag])=[Forms]![frmHighLevelReport]![cboSourceOfTag]));

[ranman256]

answered on other board.

Your WHERE may be wrong
In the access qry design, if each date range is for separate fields, then they must be on different lines of the criteria.
dept + TAG + DATE on 1 line
dept + date on line 2
...
It will look like a stair step

[John_G]

If you want all 4 conditions met in a single query, then why bother with a WHERE clause at all, given that condition 4 is "All Records"?

Am I missing somethig here?

[jj1]

Its not working. It's not giving me results I want.

As I mentioned I want 4 combinations through one query

1) Department + source of Tag+ specific Date
2) Department + Specific Date
3) Source of Tag + Specific Date
4) All records



The step layout is giving me all records not specific criteria's I want.

[John_G]

I *think* the where clause you posted is correct for your first three conditions.

But I still don't understand why you say you want "...4 combinations through one query..." when one of the conditions is "All records". Are you saying you want to be able to select which condition of the 4 you want? If so, you need 4 separate queries.

Your where clause above is for conditions 1 - 3; if it is giving you all records, then it is probably because every record satisfies at least one of those conditions.

[June7]

If you are getting all records then that means each record meets at least one of the criteria.

I'm with John_G, what you want makes no sense.