i'm using (example):

Me.Filter = "Country = 'USA'"
Me.FilterOn = True

successfully, but i'd like to filter on one column and then keep that filter as i filter on another column...?

???

thank you.

Hi,

You'll need to add a Logical And or Or to the original filter based on what you want the condition to be. For example:

Me.Filter = Me.Filter & " AND OtherColumn = 'OtherValue'"

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

thank you...got it (well, almost...)

e.g.,

name textbox
Me.Filter = "NameFull = " & "'" & Me.NameFull & " '"
Me.FilterOn = True
Me.Caption = Me.NameFull

city textbox
Me.Filter = "NameFull = '" & Me.NameFull & "'" & " And " & "city = " & "'" & Me.City & "'"
Me.FilterOn = True
Me.Caption = Me.NameFull & ", " & Me.City

year textbox
Me.Filter = "NameFull = '" & Me.NameFull & "'" & " And " & "city = " & "'" & Me.City & "'" & " and " & "Yearof = " & Me.Yearof
Me.FilterOn = True
Me.Caption = Me.NameFull & " - " & Me.City & ", " & Me.Yearof


thank you...!

Hi,

(IMG:style_emoticons/default/yw.gif)

What if the user only wants the name and year? Or city and year?

The usual approach is to build the filter up, one condition at a time. For example:

'user wants fullname
strFilter = strFilter & "NameFull='" & Me.NameFull & "' AND "

'user wants city
strFilter = strFilter & "City='" & Me.City & "' AND "

'user wants year
strFilter = strFilter & "YearOf=" & Me.YearOf & " AND "

If strFilter > "" Then
strFilter = Left(strFilter, Len(strFilter)-5)
End If

Me.Filter = strFilter
Me.FilterOn = True


Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

thank you...point well made...

i have the filter on the double click of the text box (continuous form) and this works well left to right across my three columns, but your point is well made...

where would i put the code (to follow your idea)...?

thanks.

You would put it in the place where the filter is applied. Sorry, can't give you a specific answer without knowing anything about your setup.

If you do a quick search in the Code Archive, there are a few samples on how to create "search forms" or "query by forms" that you could use as a reference.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

ok...i looked around the archives some...

and

if strFilter > "" Then
strFilter = Left(strFilter, Len(strFilter)-5)
End If

?how's does strfilter concatenate?
?why len -5?

thank you

strFilter is put together (concatenated) by the previous codes (one for each condition - fullname, city, and year).

The strFilter > "" is to check if the user wanted any filtering to be done. If none of the conditions exist, the value of strFilter would have been "".

The Len()-5 is to get rid of the last " AND " in the concatenated string. If you step through the code and watch the variable strFilter, you will see that it has the " AND " at the end that we need to remove before assigning its value to the form's filter property.

Hope that makes sense...

thank you...doing better, but how does the concatenation happen...?

in my code below (under each record's field, they don't concatenate)...

Private Sub LogInDate_Click()
'user wants city
strFilter = strFilter & "LogInDate='" & Me.LogInDate & "' AND "


End Sub

Private Sub UserName_Click()
'user wants fullname
strFilter = strFilter & "userName='" & Me.UserName & "' AND "

End Sub

???

thank you.

Ahh, I see what you mean. But like I said earlier, it's best to place the code where the filtering has to happen. So, for example, let's say you have three comboboxes on the form that users can select an item to filter the form. After they make their choices, you could give them a button to click where the filter is the applied.

What that means is that you don't need to put any code in the comboboxes, but just use the Click event of the button to concatenate the filters and then apply it.

Hope that helps...

thank you...

ok...(maybe i have it-the code under the button...)-i will try ASAP tomorrow...

i see that i will have three strfilter, so could/should i use a do loop to concatenate?

thank you (closer...(IMG:style_emoticons/default/smile.gif)

If you do it like I laid out above, you'll only have one strFilter, so you won't need a loop.

Let us know how it goes...

thank you!...(i made strfilter public-then all worked...(IMG:style_emoticons/default/smile.gif)

well, i like it...all combinations of fields seem to work...(after selection and then click on Filter button)

but...
in the prior way, (Me.Filter = "NameFull = '" & Me.NameFull & "'" & " And " & "city = " & "'" & Me.City & "'" & " and " & "Yearof = " & Me.Yearof
''Me.FilterOn = True)
i drilled in/down field by the double click on each field (left to right, but it seemed intutitive....)


can i still do that here? i.e., drill in/filter by field (prior to the final selection)?

I don't see why not, especially now that you have made your variable public. However, you may have to examine its contents first before modifying it.

Give it a try and let us know how it goes.

Cheers (IMG:style_emoticons/default/cheers.gif)

well, filter works, but not the drill down by column selection..

the following works well for the first selection, but the following selections mess up strfilter (e.g., we sometimes lose the AND from the previouse filteron...?

Public Sub NameFull_DblClick(Cancel As Integer)
strfilter = strfilter & "Namefull='" & Me.NameFull & "' AND "

If strfilter > "" Then
strfilter = Left(strfilter, Len(strfilter) - 5)
End If
Me.Filter = strfilter
Me.FilterOn = True

???

thank you.

Hi,

Did you base your filter implementation from any demos in the Archive? If so, which one?

i might have it...! (thank you)

under each of the three text boxes i have (and change field for each):

If strfilter = "" Then
strfilter = strfilter & "Namefull='" & Me.NameFull & "' AND "
If strfilter > "" Then
strfilter = Left(strfilter, Len(strfilter) - 5)
End If
Me.Filter = strfilter
Me.FilterOn = True
Else
strfilter = strfilter & " and "
strfilter = strfilter & "Namefull='" & Me.NameFull & "' AND "
If strfilter > "" Then
strfilter = Left(strfilter, Len(strfilter) - 5)
End If
Me.Filter = strfilter
Me.FilterOn = True
End If

thank you...!

Hmm... I know I am not there to see what you're doing but if you think you got it, Congratulations!

Try the following simple test and let me know if your code is still working as desired:

1. Select a FullName from the combobox and verify that the form is filtered to that name.
2. After that, select a different FullName from the combobox and verify that the form is now filtered to the new selected name and not showing any of the previous name you selected in step 1.

Did it work?

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

ok...(my filters are not on the combo box, but on the text box's double click)

#1. yes.
then i would clear filter (or continue to filter by city and/or year...)
#2. yes (select another)

in the meantime, whether you select one, two or three fields, you can select Apply at any time, to findfirst the form below...

ok...?

thank you.

Hi,

I didn't mean for you to clear the filter in between steps 1 and 2 above, but okay... Good luck with your project! (IMG:style_emoticons/default/thumbup.gif)