Solved Query with multiple parameters not working

[AlliCarr]

Hi,

I have created a query to enable staff to report on monitoring reports for our grants and determine how many we have outstanding and how long organisations took to send in their monitoring etc. I need to be able to filter the query by our respective countries and also by status. However, the query won't filter the country ID when I include the filter by 'Active' OR 'Completed' and returns all records instead. If I only filter by Active and filter by country ID it works fine. Am I missing something?

I have used the query builder in Access but the SQL is below:

SELECT
    GrantsT.GrantURN,
    GrantsT.OrganisationName,
    GrantsT.CountryID,
    GrantsT.Status,
    GrantsT.StatusDate,
    GrantsT.ProjectStart,
    GrantsT.ImpactDue,
    GrantsT.Received,
    GrantsT.ProjectCompleted,
    GrantsT.EndOfProjectVisit,
    [Received] - [ImpactDue] AS [Days Overdue]
FROM
    GrantsT
WHERE
    (
        ((GrantsT.CountryID) = 3)
        AND ((GrantsT.Status) = "Active")
    )
    OR (((GrantsT.Status) = "Completed"))
ORDER BY
    GrantsT.ImpactDue;

Any help would be appreciated.

 

[cheekybuddha]

I'm not sure I understand your requirement completely, but try it this way:

SELECT
    GrantsT.GrantURN,
    GrantsT.OrganisationName,
    GrantsT.CountryID,
    GrantsT.Status,
    GrantsT.StatusDate,
    GrantsT.ProjectStart,
    GrantsT.ImpactDue,
    GrantsT.Received,
    GrantsT.ProjectCompleted,
    GrantsT.EndOfProjectVisit,
    [Received] - [ImpactDue] AS [Days Overdue]
FROM
    GrantsT
WHERE GrantsT.CountryID = 3
  AND GrantsT.Status IN ("Active", "Completed")
ORDER BY
    GrantsT.ImpactDue;

 

[GPGeorge]

You might also want to account for possible Nulls in CountryID

WHERE    (GrantsT.CountryID = 3 AND GrantsT.Status = "Active")  
OR (GrantsT.Status = "Completed" AND GrantsT.CountryID IS NOT NULL)

That said, David's version is more likely to produce the correct result.

 

[arnelgp]

try this also:

SELECT
    GrantsT.GrantURN,
    GrantsT.OrganisationName,
    GrantsT.CountryID,
    GrantsT.Status,
    GrantsT.StatusDate,
    GrantsT.ProjectStart,
    GrantsT.ImpactDue,
    GrantsT.Received,
    GrantsT.ProjectCompleted,
    GrantsT.EndOfProjectVisit,
    [Received] - [ImpactDue] AS [Days Overdue]
FROM
    GrantsT
WHERE
        (GrantsT.CountryID = 3
        AND (GrantsT.Status = "Active" OR GrantsT.Status = "Completed"))
ORDER BY
    GrantsT.ImpactDue;

 

[cheekybuddha]

Arnel's SQL is effectively identical to mine.

 

[AlliCarr]

Thanks all, solved it using your suggestion @cheekybuddha