Add a Date Range to a Select Distinct Query

[adibakale]

I am having trouble figuring this out. I have a SELECT DISTINCT query, which is working fine. In the table that the query is based on, there is a Date Column. I would like to add BETWEEN DATE RANGE criteria to this query, but I have been unable to do so. If I add the date column to the query, the distinct query does not work as it should since it consists of different dates obviously. I tried adding the BETWEEN DATES in the Criteria/where column but that did not work. Any help with this would be greatly appreciated.



Below is the SQL Code without the date column added. The table/field name that I need to specify the date range is tbl_Transactions.Reviewed_Date

SQL Code:

SELECT DISTINCT tbl_CardHolder.SSN, tbl_Transactions.Reviewed, Count(tbl_Transactions.Reviewed) AS CountOfReviewed, tbl_CardHolder.CHNAME
FROM tbl_CardHolder RIGHT JOIN tbl_Transactions ON tbl_CardHolder.ID = tbl_Transactions.CardHolder
GROUP BY tbl_CardHolder.SSN, tbl_Transactions.Reviewed, tbl_CardHolder.CHNAME
HAVING (((tbl_Transactions.Reviewed)=True));

Here is a link to the Access Query design image:

Access Q - Imgur

 

[Joe4]

You can place your criteria in a WHERE clause (instead of the HAVING clause). Then it will exclude those dates, and you don't event have to return that Date field in your query.

So it will look something like:

[COLOR=#333333]SELECT DISTINCT tbl_CardHolder.SSN, tbl_Transactions.Reviewed, Count(tbl_Transactions.Reviewed) AS CountOfReviewed, tbl_CardHolder.CHNAME[/COLOR]
[COLOR=#333333]FROM tbl_CardHolder RIGHT JOIN tbl_Transactions ON tbl_CardHolder.ID = tbl_Transactions.CardHolder
[/COLOR][B]WHERE[/B] [B]tbl_Transactions.Reviewed_Date ...[/B]
[COLOR=#333333]GROUP BY tbl_CardHolder.SSN, tbl_Transactions.Reviewed, tbl_CardHolder.CHNAME[/COLOR]
[COLOR=#333333]HAVING (((tbl_Transactions.Reviewed)=True));[/COLOR]

(left it for you to fill in your date range)