Multiple Criteria in Query (1 Viewer)

F

freidaf

Registered User.
Local time
Yesterday, 18:11
Joined
Aug 13, 2012
Messages
44
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;
 

Attachments

  • Query.docx
    91.7 KB · Views: 69
Ranman256

Ranman256

Well-known member
Local time
Yesterday, 21:11
Joined
Apr 9, 2015
Messages
4,337
instead of making Where clause that includes every possible outcome,
make the where with only the items used:

build the 'where' clause by cycling thru all the controls....
it executes after a find button CLICK event
if null, ignore.
if not, apply.

'alter the query with the criteria....

Code: 
'----------------
sub btnOpenQry_click()
'----------------
dim sWhere as string 
dim qdf as querydef

sWhere = " where 1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"

'IN SQL
ssql = "Select * from table " & swhere
'submit sql


'IN ACCESS
set qdf = currentdb.querydefs("qsMyQry")
qdf.sql = "Select * from table " & swhere
qdf.close
docmd.openquery qdf.name

end sub
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 18:11
Joined
Aug 30, 2003
Messages
36,125
That is ugly, but the direct answer is that the date criteria has to be repeated on all the "OR" lines.
 
F

freidaf

Registered User.
Local time
Yesterday, 18:11
Joined
Aug 13, 2012
Messages
44
Thank you both for your replies. I got it to work by doing as pbaldy suggested. It worked but it's more ugly than before!

Thank you again for your help!
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 18:11
Joined
Aug 30, 2003
Messages
36,125
Happy to help. When I have a bunch of optional parameters, I tend to build the SQL in code like Ranman demonstrated. There's a sample db here that demonstrates one method:

http://www.baldyweb.com/BuildSQL.htm
 
M

Mark_

Longboard on the internet
Local time
Yesterday, 18:11
Joined
Sep 12, 2017
Messages
2,111
freidaf said:
(((tblQDetail.QD_ProdDte) Between [Forms]![frmPopupBatchesSampled]![txtStartDate] And [Forms]![frmPopupBatchesSampled]![txtEndDate])
Click to expand...

If you have a "Start Date" and an "End Date", most users will assume if they leave either blank it will be read as "Later than Start Date" OR "Before End Date". You may want to do this in code rather than trying to force a date range to be entered.

Check if there are both a start AND end dates. If only one was entered, use either >= or <= tblQDetail.DQ_ProdDte so your users get what would feel intuitive. Additionally you get to learn how to play with SQL for filtering and setting filters. Paul has a very good resource that you would be remiss to ignore.
 
Last edited:
F

freidaf

Registered User.
Local time
Yesterday, 18:11
Joined
Aug 13, 2012
Messages
44
Thank you pbaldy, this information is great! Mark thank you as well for the good advice. I appreciate your help!
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 18:11
Joined
Aug 30, 2003
Messages
36,125
No problem, hope it helps.
 
You must log in or register to reply here.

Similar threads

D
Problem with IIF statement and filtering a query
Replies
4
Views
568
Pat Hartman
P
O
Changing Query Criteria
Replies
2
Views
387
CJ_London
C
Kayleigh
Solved Fill in 0 in crosstab query
Replies
11
Views
423
CJ_London
C
N
Can't get query criteria to read a form text field
Replies
9
Views
443
Pat Hartman
P
R
Solved Query criteria on string to return all or something
Replies
14
Views
592
cheekybuddha
cheekybuddha

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom