How to filter in a form via query

[sueb]

My database has a form which all the users use, and I need to add a button to filter the dataset based on the values in three separate fields. I tried a really simplistic approach, and went to Advanced Filter, but couldn't really figure out how to achieve the "or" that I needed (i.e., include this record in the dataset if any of three fields is "Yes"). I actually have no idea where to start with this. (The fields are all Yes/No fields, if that helps any.)

Thanks for any help you can give me!

[patjones]

Hi,

You can specify the filter just like how you would write out the WHERE part of a query. So in the On Click event for the button you can put something like

Me.Filter = "SomeField1 = SomeCondition1 OR SomeField2 = SomeCondition2 OR SomeField3 = SomeCondition3"

This will work provided that the fields you are testing are specified in the form's recordsource. Of course, if the conditions are derived from boxes on the form, it takes slightly more work:

Me.Filter = "SomeField1 = " & Me.txtBox1 & " OR SomeField2 = " & Me.txtBox2 & " OR SomeField3 = " & txtBox3

Pat

[NeoPa]

Alternatively, as FALSE values are zero, you could simply use a filter of :

[CheckBox1] + [CheckBox2] + [CheckBox3]

If all the CheckBoxes are FALSE, then the result will be FALSE. Otherwise it will not be.

[sueb]

Alternatively, as FALSE values are zero, you could simply use a filter of :

[CheckBox1] + [CheckBox2] + [CheckBox3]

If all the CheckBoxes are FALSE, then the result will be FALSE. Otherwise it will not be.

ah-HA! That's the kind of thing I was thinking of trying (adding the values). I think that's the way I'll go for this one.

Okay, but now I'm torn about best-ing, since zepphead80's was so complete (with the extremely useful stuff about conditions derived from boxes, which I very well may need later) and NeoPa's answer is so elegant!

[patjones]

ah-HA! That's the kind of thing I was thinking of trying (adding the values). I think that's the way I'll go for this one.

Okay, but now I'm torn about best-ing, since zepphead80's was so complete (with the extremely useful stuff about conditions derived from boxes, which I very well may need later) and NeoPa's answer is so elegant!

Don't worry about that at all. I happen to like NeoPa's way as well and will not be the slightest bit offended!

Pat

[NeoPa]

and NeoPa's answer is so elegant!

And they say the way to a man's heart is through his stomach?

Such compliments are always welcome. Thank you :)

[sueb]

So I've just now gotten a chance to try this, but when I click my command button, nothing at all happens. Here's my code:

Private Sub All_RN_Reviews_Click()
On Error GoTo Err_All_RN_Reviews_Click

    Me.Filter = "Me.RN_Review = -1 OR Me.RN_Review__2_ = -1 OR Me.RN_Review__3_ = -1"

Exit_All_RN_Reviews_Click:
    Exit Sub

Err_All_RN_Reviews_Click:
    MsgBox Err.Description
    Resume Exit_All_RN_Reviews_Click

End Sub

What I was expecting was that I would get a subset of my records, each of which would have at least one of the specified "RN_Review" tags set.

[patjones]

I think there are two things going on. After you define the filter, you need to turn it on by going Me.FilterOn = True. However, I'm not so sure about the filter itself. Are RN_Review and RN_Review__2_ and RN_Review__3_ the actual column names in the recordsource of the form, or just the names of the text boxes?

Pat

[NeoPa]

Sue, You're trying to refer to the controls on the form (within a string) instead of the fields in the underlying recordset.
Try instead :

Me.Filter = "[RN_Review] OR [RN_Review__2_] OR [RN_Review__3_]"

I don't know what your fieldnames are, but they need to match the filter string to work of course.

I'm not sure off the top of my head whether or not you need to set Me.FilterOn to True explicitly.

[sueb]

Sue, You're trying to refer to the controls on the form (within a string) instead of the fields in the underlying recordset.
Try instead :

Me.Filter = "[RN_Review] OR [RN_Review__2_] OR [RN_Review__3_]"

I don't know what your fieldnames are, but they need to match the filter string to work of course.

I'm not sure off the top of my head whether or not you need to set Me.FilterOn to True explicitly.

Okay, so now the line reads:

Me.Filter = "[RN_Review] Or [RN_Review__2_] Or [RN_Review__3_]"

This, at least, tries to do something: it asks me for a value for each field. If I put in -1 or 1, it returns all records; if I put in 0, it returns an empty record. (I did explicitly turn on the filter.)

[sueb]

I think there are two things going on. After you define the filter, you need to turn it on by going Me.FilterOn = True. However, I'm not so sure about the filter itself. Are RN_Review and RN_Review__2_ and RN_Review__3_ the actual column names in the recordsource of the form, or just the names of the text boxes?

Pat

I did turn on the filter. And, yes, those are the real field names in the record because whoever set it up named them "RN Review" and "RN Review (2)" and "RN Review (3)". I chose the names in the code off the little drop-down list that shows up, so I'm assuming this is VB's way of handling spaces and parentheses.

[patjones]

I did turn on the filter. And, yes, those are the real field names in the record because whoever set it up named them "RN Review" and "RN Review (2)" and "RN Review (3)". I chose the names in the code off the little drop-down list that shows up, so I'm assuming this is VB's way of handling spaces and parentheses.

So does it work, or is it still doing nothing?

[sueb]

In my reply to NeoPa, you can see the line as it now stands, and read how it now does actually do something, but definitely not what I expected.

[patjones]

In my reply to NeoPa, you can see the line as it now stands, and read how it now does actually do something, but definitely not what I expected.

Yes, sorry - I didn't see that reply.

If it is asking you for values, it means that Access cannot find those column names anywhere in your recordsource, so it would seem there is a discrepancy between how the columns are actually named and how you wrote them out in the filter.

If the field names are written out as you just specified, maybe the filter should read as follows?

Me.Filter = "[RN Review] Or [RN Review (2)] Or [RN Review (3)]"

Pat

[NeoPa]

In view of what Sue says Pat, I expect that's certainly what it should be.