Works in the Query - Why can't my filter do it??

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

I have a query that Pulls 5 separate tables together.

Table A has the major portion of data, and the Primary Key = to all
the other Primary Keys.

I bring 2 field from each of tables B, C, D, & E.
Then I define the criteria of fields in B, C, & D that they must >
field E.

This is working and I can, I can base my form on it.
HOWEVER... I don't want to base my form on it. I want my form
unrestricted, and only apply this restriction (filter?) by pressing a
command button.

I've tried: DoCmd ApplyFilter FieldB>FieldE Or FieldC>FieldE....etc.
I get nothing from this. It appears to run and I have no errors, but
get no filter. I've tried brackets, parethesis, etc. Nothing.

I'm at the point after several different methods of cheating and just
having my command button close the form and open a new form based on
the working query, but that seems pretty cheap, and I'm hoping to
avoid it.

Help? Thanks!
Tim
 
A filter needs to be a string:

DoCmd ApplyFilter "FieldB>FieldE Or FieldC>FieldE...."
 
Tim,

I use the forms Filter and FilterOn methods

Private Sub cmd_Filter_Click()

me.Filter = "[FieldB] > [FieldE] OR [FieldC] > [FieldE] OR [FieldD] >
[FieldE]"
me.FilterOn = Not me.FilterOn

end sub

HTH
Dale
 
Tim,

Can you post the whole SQL string that you are trying to implement?

Have you confirmed that those criteria will actually filter out some
records?
It may be that with the combination of OR statements, you actually have
filtered the dataset, but that it is returning all the records because at
least
one of the criteria is met for each record in your data set.

You might want to try a union query something like the following to
test this out. If you get the same number of records in this query as
you do in the one without the where clauses, then you know that
at least one of the criteria is met for each record.

SELECT ....
FROM yourTable
Join .....
WHERE FieldB>FieldE
UNION
SELECT ....
FROM yourTable
Join ....
WHERE FieldC > FieldE
UNION
SELECT ....
FROM yourTable
Join ....
WHERE FieldD > FieldE

HTH
Dale
 
Dale, this me.filteron=Not me.filteron was the key. Thanks for your
help!!

Tim,

I use the forms Filter and FilterOn methods

Private Sub cmd_Filter_Click()

me.Filter = "[FieldB] > [FieldE] OR [FieldC] > [FieldE] OR [FieldD] >
[FieldE]"
me.FilterOn = Not me.FilterOn

end sub

HTH
Dale

Tim said:
I have a query that Pulls 5 separate tables together.

Table A has the major portion of data, and the Primary Key = to all
the other Primary Keys.

I bring 2 field from each of tables B, C, D, & E.
Then I define the criteria of fields in B, C, & D that they must >
field E.

This is working and I can, I can base my form on it.
HOWEVER... I don't want to base my form on it. I want my form
unrestricted, and only apply this restriction (filter?) by pressing a
command button.

I've tried: DoCmd ApplyFilter FieldB>FieldE Or FieldC>FieldE....etc.
I get nothing from this. It appears to run and I have no errors, but
get no filter. I've tried brackets, parethesis, etc. Nothing.

I'm at the point after several different methods of cheating and just
having my command button close the form and open a new form based on
the working query, but that seems pretty cheap, and I'm hoping to
avoid it.

Help? Thanks!
Tim
Click to expand...
Click to expand...
 
Glad I could help

Tim said:
Dale, this me.filteron=Not me.filteron was the key. Thanks for your
help!!

Tim,

I use the forms Filter and FilterOn methods

Private Sub cmd_Filter_Click()

me.Filter = "[FieldB] > [FieldE] OR [FieldC] > [FieldE] OR [FieldD] >
[FieldE]"
me.FilterOn = Not me.FilterOn

end sub

HTH
Dale

Tim said:
I have a query that Pulls 5 separate tables together.

Table A has the major portion of data, and the Primary Key = to all
the other Primary Keys.

I bring 2 field from each of tables B, C, D, & E.
Then I define the criteria of fields in B, C, & D that they must >
field E.

This is working and I can, I can base my form on it.
HOWEVER... I don't want to base my form on it. I want my form
unrestricted, and only apply this restriction (filter?) by pressing a
command button.

I've tried: DoCmd ApplyFilter FieldB>FieldE Or FieldC>FieldE....etc.
I get nothing from this. It appears to run and I have no errors, but
get no filter. I've tried brackets, parethesis, etc. Nothing.

I'm at the point after several different methods of cheating and just
having my command button close the form and open a new form based on
the working query, but that seems pretty cheap, and I'm hoping to
avoid it.

Help? Thanks!
Tim
Click to expand...
Click to expand...
Click to expand...
 
Back
Top