Blank Report

[Anna]

Hi: Is it possible that if i leave the CRC or date blank in my form
(frmweb_Mailfilter) it will show all of the record. As right now if i
am not select any value from the form it shows the blank report.


SELECT tbl_WebMail.CRC, Sum(tbl_WebMail.WM_Amt) AS Totamnt
FROM tbl_WebMail
WHERE (((tbl_WebMail.WM_Date) Between
[Forms]![frmWeb_Mailfilter]![txtdate1] And
[Forms]![frmWeb_Mailfilter]![txtdate2]))
GROUP BY tbl_WebMail.CRC
HAVING (((tbl_WebMail.CRC)=[Forms]![frmWeb_Mailfilter]![ddlcrc]));

Thank You.

 

[John Vinson]

Hi: Is it possible that if i leave the CRC or date blank in my form
(frmweb_Mailfilter) it will show all of the record. As right now if i
am not select any value from the form it shows the blank report.

Yes. Edit the query to

SELECT tbl_WebMail.CRC, Sum(tbl_WebMail.WM_Amt) AS Totamnt
FROM tbl_WebMail
WHERE tbl_WebMail.WM_Date >=
NZ([Forms]![frmWeb_Mailfilter]![txtdate1]), #1/1/100#) And
tbl_WebMail.WM_Date) <=
NZ([Forms]![frmWeb_Mailfilter]![txtdate2], #12/31/9999#) And
(((tbl_WebMail.CRC)=[Forms]![frmWeb_Mailfilter]![ddlcrc]))
GROUP BY tbl_WebMail.CRC;

Moved the HAVING clause into the WHERE - it's much more efficient
there since it's applied before the sorting or grouping.

John W. Vinson[MVP]