Apply filter

Hello guys,
I have problem with my project and I need help with the solution.

1. Query name “A” has column1...column10
2. Form name “B” with subform “C”

In the subform “C” is connected source as query “A”...

My question is how I may update query “A” results if I want to use default filter (any search box any combobox) from all columns in the subform “C”
I thought that subform “C” is the same as query “A” but If I use filter in column1 in subform “C” then not update filtered result in query “A”

But
with one column is not problem, for example if in the query “A” for column1 give criteria Forms!B!C!column1 then from form is function update query result... but I can’t combine more criteria for each column in query “A”

Is possible please to used column filter for update query?

-In query “A” re the all data
-Users can use filter for each column in subform “C” and then export filtered data to excel with command button or print.

thanks for reply and sorry for my english ;)
Johny RamboAsked:
Who is Participating?
 
PatHartmanConnect With a Mentor Commented:
if I combine filter in fields then is not right.
What does "not right" mean?  

Are you using only AND as the relational operator?
Are all of the variables always provided or are some of them optional?  If some are optional, you can use this pattern.

WHERE (A.field1 = Forms!B!C!field1 OR Forms!B!C!field1 is Null) and (A.field2 = Forms!B!C!field2 OR Forms!B!C!field2 Is Null) ... (A.field10 = Forms!B!C!field10 Is Null)

Once an expression contains AND's and OR's, you must use parentheses to control the order of precedence. and force Access to evaluate the expression as you want it to be evaluated.
0
 
PatHartmanCommented:
Filters that you apply using the interface tools, do NOT affect the underlying query.  If you want to have the query respect the filters, you need to build your own filtering interface.  Then you would change the  WHERE clause of the query:

Select ...
From ...
Where fld1 = Forms!yourform!cbofld1 AND fld2 = Forms!yourform!cbofld2 AND fld3 = Forms!yourform!cbofld3 AND .....

Then when you export the query to Excel, the WHERE clause will filter the data as you expect it to.
0
 
Johny RamboAuthor Commented:
So in my case:

query = A
form = B
subform (source A) in form B = C

SELECT A.field1; A.field2 ... A.field10 FROM A WHERE A.field1 = Forms!B!C!field1 and A.field2 = Forms!B!C!field2 ... A.field10 = Forms!B!C!field10;

here is the same problem, if I use it only for one field then is update result query right... if I combine filter in fields then is not right.

I tried it with or but this not function too...
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.