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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Rey Obrero (Capricorn1)Commented:
can you upload a copy of the db with the table and form
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

_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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
_HectorAuthor Commented:
Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.