"Double" filter

[MrNooby]

I am now using for example:

Private Sub Button212_Click()
Me.Filter = "[CatDate] = date()"
Me.FilterOn = True
End Sub

Filter by some dates. This works fine, but is there any way I can now create another button which will filter by something else and it will include only those already filtered results?

 

[vbaInet]

The Filter property should be able to handle your second field. Use OR if you want results containing either criteria or use AND if you want it matching up both. E.g.

Me.Filter = "[CatDate] = date() OR ....."

 

[MrNooby]

I shouldn't use the same criteria in same button. The point is that someone filters by date (or anything else) and later on he can still sort by something else those results.

Is this even possible?

 

[vbaInet]

Use what you've already got and just change the name of the field. For example for the first line, if [CatName] was of type String:

Me.Filter = "[CatName] = [COLOR=Red]'[/COLOR]" & CatName_textbox.value & "[COLOR=Red]'[/COLOR]"

Another example of the first line if CatID was a Number data type:

Me.Filter = "[CatID] = " & CatID_textbox.value

The difference is highlighted in red. You need those quotes to handle String criteria.

 

[MrNooby]

True, but this will filter entire table from start, not those records which I have already filtered.

Example of table:

Name Date Person
Test1 1.2.2010 John
Test2 4.2.2010 John
Test3 1.2.2010 John
Test4 4.2.2010 George
Test5 4.2.2010 George
Test6 1.2.2010 George

I now execute this command:

Me.Filter = "[CatDate] = = #1/2/2010#"

This will sort only those with date 1/2/2010, so now I have:

Name Date Person
Test1 1.2.2010 John
Test3 1.2.2010 John
Test6 1.2.2010 George

OK, if I execute this command:

Me.Filter = "[Person] = '" & SomeTextBox.value & "'"

and if I have in SomeTextBox written John resoult will be:

Name Date Person
Test1 1.2.2010 John
Test2 4.2.2010 John
Test3 1.2.2010 John

Which is wrong, I need to sort those which ware already sorted, so correct sort would now be:

Name Date Person
Test1 1.2.2010 John
Test3 1.2.2010 John

I hope it's more understanding now

 

[SOS]

Try using this:

Me.Filter = Me.Filter & " AND [WhateverField]=" & Whatever

 

[MrNooby]

SOS, you really know everything Thank you.

Just one more question regarding this, is there any way to check with if sentence if Me.Filter was already made? If Filter wasn't made before and if I use this code then nothing is filtered.

I tried with

If Len(Me.Filter) > 0 Then

But that doesn't work.

 

[SOS]

You can use

If Me.Filter <> "" Then
   ...put the other stuff here
Else
   Me.Filter = "[Whatever]=" & Whatever
End If

 

[MrNooby]

Strange, but this code always executes "other stuff" no matter if I have used Me.Filter before or not.

 

[SOS]

Strange, but this code always executes "other stuff" no matter if I have used Me.Filter before or not.

How about changing the

If Me.Filter = "" Then

to

If Len(Me.Filter & "") = 0 Then

does that work?

 

[SOS]

You also might need to set

Me.Filter = ""

When you turn off the filter with

Me.FilterOn = False

 

[MrNooby]

Same thing...

I added MsgBox to see Len(Me.Filter) and when I open form and its value is 51... After I use some filter it's 25. Does that mean anything?

 

[SOS]

Yeah, it is a "persistent" filter thing.

When you close the form put this in the form's close event:

Me.Filter = ""

and that should help.

 

[SOS]

Oh, and check the filter property while in design view of the form. You might need to clear the filter out and then save the form so there isn't a filter stuck in there.

 

[MrNooby]

It's main form so instead of On close I placed it On open and looks like it works. While doing this I saw that I have 1 button which clears all filters with "FilterOn = False" so I tried this code:

If (FilterOn = False) Then
...Some code for no fillter made before...
Else
Me.Filter = Me.Filter & " AND [something] = ...
End If

And looks like it works great. I'll test a bit more but I think this will do it

Thanks again SOS!

 

[SOS]

Cool, glad it was helpful.