Hello,
I have a query that obtains some of its criteria from a popup form. If the criteria fields on the form are left blank, all records are returned for that field. It worked fine until I added criteria for the date range:
(((tblQDetail.QD_ProdDte) Between [Forms]![frmPopupBatchesSampled]![txtStartDate] And [Forms]![frmPopupBatchesSampled]![txtEndDate])
It won't limit the results to the date range. I need the query to limit records returned to the date range entered in the form, then filter those results by the other criteria entered into the form.
I don't use SQL when creating queries, but I copied the SQL into this post. I have also attached a picture of what my query looks like in query design view.
Can someone tell me where my problem is? Thank you for your help.
SELECT tblQDetail.QD_ProdDte, tblQDetail.QD_JD, tblQDetail.QD_WT, tblQDetail.QD_IB, Sum((IIf([QD_ActivityID] In ('QE'),[QD_QtySampled],0))) AS Embossing, Sum((IIf([QD_ActivityID] In ('QMM'),[QD_QtySampled],0))) AS MM, Sum((IIf([QD_ActivityID] In ('QI','RIS'),[QD_QtySampled],0))) AS Insertion, Sum((IIf([QD_ActivityID] In ('MEI','QLS'),[QD_QtySampled],0))) AS LS
FROM tblQDetail
WHERE (((tblQDetail.QD_ActivityID) In ('QE','QMM','QLS','MEI','QI','RIS') Or (tblQDetail.QD_ActivityID) In ('QE','QMM','QLS','MEI','QI','RIS') Or ((tblQDetail.QD_ActivityID) In ('QE','QMM','QLS','MEI','QI','RIS') Or (tblQDetail.QD_ActivityID) In ('QE','QMM','QLS','MEI','QI','RIS')) Or ((tblQDetail.QD_ActivityID) In ('QE','QMM','QLS','MEI','QI','RIS') Or (tblQDetail.QD_ActivityID) In ('QE','QMM','QLS','MEI','QI','RIS') Or ((tblQDetail.QD_ActivityID) In ('QE','QMM','QLS','MEI','QI','RIS') Or (tblQDetail.QD_ActivityID) In ('QE','QMM','QLS','MEI','QI','RIS')))))
GROUP BY tblQDetail.QD_ProdDte, tblQDetail.QD_JD, tblQDetail.QD_WT, tblQDetail.QD_IB
HAVING (((tblQDetail.QD_ProdDte) Between [Forms]![frmPopupBatchesSampled]![txtStartDate] And [Forms]![frmPopupBatchesSampled]![txtEndDate]) AND ((tblQDetail.QD_JD)=[Forms]![frmPopupBatchesSampled]![txtJD]) AND ((tblQDetail.QD_WT)=[Forms]![frmPopupBatchesSampled]![txtWT]) AND ((tblQDetail.QD_IB)=[Forms]![frmPopupBatchesSampled]![txtIB])) OR (((tblQDetail.QD_WT)=[Forms]![frmPopupBatchesSampled]![txtWT]) AND ((tblQDetail.QD_IB)=[Forms]![frmPopupBatchesSampled]![txtIB]) AND ((([tblQDetail].[QD_JD]) Like [Forms]![frmPopupBatchesSampled]![txtJD]) Is Null)) OR (((tblQDetail.QD_JD)=[Forms]![frmPopupBatchesSampled]![txtJD]) AND ((tblQDetail.QD_IB)=[Forms]![frmPopupBatchesSampled]![txtIB]) AND ((([tblQDetail].[QD_WT]) Like [Forms]![frmPopupBatchesSampled]![txtWT]) Is Null)) OR (((tblQDetail.QD_IB)=[Forms]![frmPopupBatchesSampled]![txtIB]) AND ((([tblQDetail].[QD_JD]) Like [Forms]![frmPopupBatchesSampled]![txtJD]) Is Null) AND ((([tblQDetail].[QD_WT]) Like [Forms]![frmPopupBatchesSampled]![txtWT]) Is Null)) OR (((tblQDetail.QD_JD)=[Forms]![frmPopupBatchesSampled]![txtJD]) AND ((tblQDetail.QD_WT)=[Forms]![frmPopupBatchesSampled]![txtWT]) AND ((([tblQDetail].[QD_IB]) Like [Forms]![frmPopupBatchesSampled]![txtIB]) Is Null)) OR (((tblQDetail.QD_WT)=[Forms]![frmPopupBatchesSampled]![txtWT]) AND ((([tblQDetail].[QD_JD]) Like [Forms]![frmPopupBatchesSampled]![txtJD]) Is Null) AND ((([tblQDetail].[QD_IB]) Like [Forms]![frmPopupBatchesSampled]![txtIB]) Is Null)) OR (((tblQDetail.QD_JD)=[Forms]![frmPopupBatchesSampled]![txtJD]) AND ((([tblQDetail].[QD_WT]) Like [Forms]![frmPopupBatchesSampled]![txtWT]) Is Null) AND ((([tblQDetail].[QD_IB]) Like [Forms]![frmPopupBatchesSampled]![txtIB]) Is Null)) OR (((([tblQDetail].[QD_JD]) Like [Forms]![frmPopupBatchesSampled]![txtJD]) Is Null) AND ((([tblQDetail].[QD_WT]) Like [Forms]![frmPopupBatchesSampled]![txtWT]) Is Null) AND ((([tblQDetail].[QD_IB]) Like [Forms]![frmPopupBatchesSampled]![txtIB]) Is Null))
ORDER BY tblQDetail.QD_JD, tblQDetail.QD_WT, tblQDetail.QD_IB;
I have a query that obtains some of its criteria from a popup form. If the criteria fields on the form are left blank, all records are returned for that field. It worked fine until I added criteria for the date range:
(((tblQDetail.QD_ProdDte) Between [Forms]![frmPopupBatchesSampled]![txtStartDate] And [Forms]![frmPopupBatchesSampled]![txtEndDate])
It won't limit the results to the date range. I need the query to limit records returned to the date range entered in the form, then filter those results by the other criteria entered into the form.
I don't use SQL when creating queries, but I copied the SQL into this post. I have also attached a picture of what my query looks like in query design view.
Can someone tell me where my problem is? Thank you for your help.
SELECT tblQDetail.QD_ProdDte, tblQDetail.QD_JD, tblQDetail.QD_WT, tblQDetail.QD_IB, Sum((IIf([QD_ActivityID] In ('QE'),[QD_QtySampled],0))) AS Embossing, Sum((IIf([QD_ActivityID] In ('QMM'),[QD_QtySampled],0))) AS MM, Sum((IIf([QD_ActivityID] In ('QI','RIS'),[QD_QtySampled],0))) AS Insertion, Sum((IIf([QD_ActivityID] In ('MEI','QLS'),[QD_QtySampled],0))) AS LS
FROM tblQDetail
WHERE (((tblQDetail.QD_ActivityID) In ('QE','QMM','QLS','MEI','QI','RIS') Or (tblQDetail.QD_ActivityID) In ('QE','QMM','QLS','MEI','QI','RIS') Or ((tblQDetail.QD_ActivityID) In ('QE','QMM','QLS','MEI','QI','RIS') Or (tblQDetail.QD_ActivityID) In ('QE','QMM','QLS','MEI','QI','RIS')) Or ((tblQDetail.QD_ActivityID) In ('QE','QMM','QLS','MEI','QI','RIS') Or (tblQDetail.QD_ActivityID) In ('QE','QMM','QLS','MEI','QI','RIS') Or ((tblQDetail.QD_ActivityID) In ('QE','QMM','QLS','MEI','QI','RIS') Or (tblQDetail.QD_ActivityID) In ('QE','QMM','QLS','MEI','QI','RIS')))))
GROUP BY tblQDetail.QD_ProdDte, tblQDetail.QD_JD, tblQDetail.QD_WT, tblQDetail.QD_IB
HAVING (((tblQDetail.QD_ProdDte) Between [Forms]![frmPopupBatchesSampled]![txtStartDate] And [Forms]![frmPopupBatchesSampled]![txtEndDate]) AND ((tblQDetail.QD_JD)=[Forms]![frmPopupBatchesSampled]![txtJD]) AND ((tblQDetail.QD_WT)=[Forms]![frmPopupBatchesSampled]![txtWT]) AND ((tblQDetail.QD_IB)=[Forms]![frmPopupBatchesSampled]![txtIB])) OR (((tblQDetail.QD_WT)=[Forms]![frmPopupBatchesSampled]![txtWT]) AND ((tblQDetail.QD_IB)=[Forms]![frmPopupBatchesSampled]![txtIB]) AND ((([tblQDetail].[QD_JD]) Like [Forms]![frmPopupBatchesSampled]![txtJD]) Is Null)) OR (((tblQDetail.QD_JD)=[Forms]![frmPopupBatchesSampled]![txtJD]) AND ((tblQDetail.QD_IB)=[Forms]![frmPopupBatchesSampled]![txtIB]) AND ((([tblQDetail].[QD_WT]) Like [Forms]![frmPopupBatchesSampled]![txtWT]) Is Null)) OR (((tblQDetail.QD_IB)=[Forms]![frmPopupBatchesSampled]![txtIB]) AND ((([tblQDetail].[QD_JD]) Like [Forms]![frmPopupBatchesSampled]![txtJD]) Is Null) AND ((([tblQDetail].[QD_WT]) Like [Forms]![frmPopupBatchesSampled]![txtWT]) Is Null)) OR (((tblQDetail.QD_JD)=[Forms]![frmPopupBatchesSampled]![txtJD]) AND ((tblQDetail.QD_WT)=[Forms]![frmPopupBatchesSampled]![txtWT]) AND ((([tblQDetail].[QD_IB]) Like [Forms]![frmPopupBatchesSampled]![txtIB]) Is Null)) OR (((tblQDetail.QD_WT)=[Forms]![frmPopupBatchesSampled]![txtWT]) AND ((([tblQDetail].[QD_JD]) Like [Forms]![frmPopupBatchesSampled]![txtJD]) Is Null) AND ((([tblQDetail].[QD_IB]) Like [Forms]![frmPopupBatchesSampled]![txtIB]) Is Null)) OR (((tblQDetail.QD_JD)=[Forms]![frmPopupBatchesSampled]![txtJD]) AND ((([tblQDetail].[QD_WT]) Like [Forms]![frmPopupBatchesSampled]![txtWT]) Is Null) AND ((([tblQDetail].[QD_IB]) Like [Forms]![frmPopupBatchesSampled]![txtIB]) Is Null)) OR (((([tblQDetail].[QD_JD]) Like [Forms]![frmPopupBatchesSampled]![txtJD]) Is Null) AND ((([tblQDetail].[QD_WT]) Like [Forms]![frmPopupBatchesSampled]![txtWT]) Is Null) AND ((([tblQDetail].[QD_IB]) Like [Forms]![frmPopupBatchesSampled]![txtIB]) Is Null))
ORDER BY tblQDetail.QD_JD, tblQDetail.QD_WT, tblQDetail.QD_IB;