Results 1 to 3 of 3
  1. #1
    Join Date
    May 2014
    Posts
    98

    Unanswered: Query Not Populating multiple results

    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]));

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so its going to be a problem with your bracketing together of terms
    ...however in the meantime why do you select the same column more than once

    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]

    [Unsafe Act Unsafe Condition].* means return all columns in table [Unsafe Act Unsafe Condition]
    so there is no need to add the other columns from the same table. OR better yet explicitly select which columns you need as opposed to grabbing every column in the table. its probably not an issue here but it can lead to perfomance problems over time with humungous datasets.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    496
    Provided Answers: 24
    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

    Click image for larger version. 

Name:	OR.png 
Views:	1 
Size:	5.8 KB 
ID:	15907

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •