Filter Query using multiple form values

I would like the query below to first find the records where field IFQOH<AVG60OH next from those results only display records that match the values present on my form. If no values are present on my form then the original results are displayed. I can't get this to work right. Thanks!


SELECT [REPORT DBASE].IFITEM, [REPORT DBASE].ICDSC1, [REPORT DBASE].IFQOH, [REPORT DBASE].IFQPO, [REPORT DBASE].IFQBO, [REPORT DBASE].IFAVU, [REPORT DBASE].IFTRAN, [REPORT DBASE].IFQBT, [REPORT DBASE].AVGX6, [REPORT DBASE].IFSMTD, [REPORT DBASE].ICMAST
FROM [REPORT DBASE]
WHERE ((([REPORT DBASE].IFQOH)<[REPORT DBASE]!AVG60OH)) And ((([REPORT DBASE].ICDIV)=Forms![Master Form]!Division) And (([REPORT DBASE].ICCLS)=Forms![Master Form]!Class)) Or ((([REPORT DBASE].ICCLS)=Forms![Master Form]!Class) And ((Forms![Master Form]!Division) Is Null)) Or ((([REPORT DBASE].ICDIV)=Forms![Master Form]!Division) And ((Forms![Master Form]!Class) Is Null)) Or (((Forms![Master Form]!Division) Is Null) And ((Forms![Master Form]!Class) Is Null))
ORDER BY [REPORT DBASE].IFITEM;
_HectorAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
here is your revised db

run query2 first and take note how many record returned

enter a value for division and hit the tab key  and run query3
take note of how many records returned

close query3

enter a value in class anf hit the tab key
run query3
Example.mdb
0
 
Rey Obrero (Capricorn1)Commented:
try changing the "And" to "Or"

SELECT [REPORT DBASE].IFITEM, [REPORT DBASE].ICDSC1, [REPORT DBASE].IFQOH, [REPORT DBASE].IFQPO, [REPORT DBASE].IFQBO, [REPORT DBASE].IFAVU, [REPORT DBASE].IFTRAN, [REPORT DBASE].IFQBT, [REPORT DBASE].AVGX6, [REPORT DBASE].IFSMTD, [REPORT DBASE].ICMAST
FROM [REPORT DBASE]
WHERE ((([REPORT DBASE].IFQOH)<[REPORT DBASE]!AVG60OH)) OR ((([REPORT DBASE].ICDIV)=Forms![Master Form]!Division) And (([REPORT DBASE].ICCLS)=Forms![Master Form]!Class)) Or ((([REPORT DBASE].ICCLS)=Forms![Master Form]!Class) And ((Forms![Master Form]!Division) Is Null)) Or ((([REPORT DBASE].ICDIV)=Forms![Master Form]!Division) And ((Forms![Master Form]!Class) Is Null)) Or (((Forms![Master Form]!Division) Is Null) And ((Forms![Master Form]!Class) Is Null))
ORDER BY [REPORT DBASE].IFITEM;
0
 
_HectorAuthor Commented:
capricorn1,
when I do that I get all the records in the file without any filtering.

Hector
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rey Obrero (Capricorn1)Commented:
can you upload a copy of the db with the table and form
0
 
_HectorAuthor Commented:
Yes,
Thank you.

 Example.zip
0
 
Rey Obrero (Capricorn1)Commented:

create a query with this statement and save as "query2"

SELECT [REPORT DBASE].IFITEM, [REPORT DBASE].ICDSC1, [REPORT DBASE].IFQOH, [REPORT DBASE].IFQPO, [REPORT DBASE].IFQBO, [REPORT DBASE].IFAVU, [REPORT DBASE].IFTRAN, [REPORT DBASE].IFQBT, [REPORT DBASE].AVGX6, [REPORT DBASE].IFSMTD, [REPORT DBASE].ICMAST, [REPORT DBASE].ICDIV, [REPORT DBASE].ICCLS
FROM [REPORT DBASE]
WHERE ((([REPORT DBASE].IFQOH)<[REPORT DBASE]![AVG60OH]));

then create another query, using "query2" as the domain

SELECT Query2.IFITEM, Query2.ICDSC1, Query2.IFQOH, Query2.IFQPO, Query2.IFQBO, Query2.IFAVU, Query2.IFTRAN, Query2.IFQBT, Query2.AVGX6, Query2.IFSMTD, Query2.ICMAST
FROM Query2
WHERE (((Query2.ICDIV)=Nz([Forms]![Master Form]![Division],[ICDIV])) AND ((Query2.ICCLS)=Nz([Forms]![Master Form]![Class],[ICCLS])))
ORDER BY Query2.IFITEM;


post back your findings


0
 
_HectorAuthor Commented:
capricorn1:

I'm unsure if I properly setup the 2nd query making query2 it's domain.(never did this before) I ran it and I get the results of query2 which look good. But when I specify additional criteria on the form in the division or class field it's ignored and I still get the query2 results.


0
 
_HectorAuthor Commented:
Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.