Can't filter more than 1 field at a time VBA

[achammar]

I'm trying to open a table with 2 fields filtered using VBA. It works fine with only 1 field filtered, but I want to filter more than 1 field.
This code will only apply the filter to whichever one is the last one. So this code would only apply a filter to the field named "Part_Type" with the value filtered as "Plate"

DoCmd.OpenTable "tblSheetPlate"


DoCmd.ApplyFilter , "[Material] = 'Stainless'"
DoCmd.ApplyFilter , "[Part_Type] = 'Plate'"


The previous line does not work. I can filter the table manually by as many fields as I want. Can I not filter more than 1 field with VBA??? or what am I doing wrong?

Thank you so much!

[ranman256]

sub btnFind_click()
sWhere = "1=1"
if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(cboGender) then    sWhere = sWhere & " and [Gender]='" & cboGender & "'"

 'then filer
if sWhere = "1=1" then
  me.filterOn = false
else
  me.filter = sWhere
  me.filterOn = true
endif
end sub

[achammar]

I don't think that has anything to do with the question I asked. I'm just trying to open a table to view it with a filter applied to 2 fields. your code is way beyond that I think. lol

[ranman256]

you cant apply filters 1 on top of anther.
you must give 1 filter and use AND to make them work together

filter: A and B and C

[achammar]

I only want 1 filter but in 2 different columns in my table. So one in each column. I want to filter 1 column for "Plate", and a different column for "Stainless", so I end up with only records that are plate in one column and stainless in the other column. I can do that manually with the filter option at the top of each column on the table. You're saying I can't do that with VBA?

[achammar]

Ok, I figured it out by using "AND" between them instead of putting them on separate lines. Thanks!

[achammar]

Ok, now that I got that to work and I can filter multiple columns in my table now, do you know what code I can use to turn just one of them off?

[orange]

Look at your query SQL or your vba. Copy and post it here with info on which filter(s) you want.
You'll get very focused responses.
Good luck.

[achammar]

This is the code I'm using now to filter 2 fields in a table when it opens:

DoCmd.OpenTable "tblSheetPlate"


DoCmd.ApplyFilter , "[Part_Type] = 'Plate' AND [Material] = 'stainless'"

It works good. Now I want to be able to turn only one of them off with VBA. Any way to do that without using 'ShowAllRecords' because that removes all filters?

Thanks!!

[orange]

How did you decide on 1 filter for Part_Type and Material?

Consider:You could have a question of the form open.

Which filter to apply?
Part_Type
Material
Or Both

Often done with an Option Group. See this video for details.
Then, a Select Case statement where, based on what is selected in the option group
you have a Filter expression as appropriate.

Good luck.

[achammar]

I was trying to make it simple in my post. I am opening a table, not a form. I am clicking a button on a form that opens the table and filters it. It gets it filter criteria from 2 combo boxes that are on that same form that I am opening the table from. In some cases I will not want a filter for one of the fields. But I can't leave that combo box blank otherwise it filters for blanks and returns no records. So the only way I know how to work around that, if it is blank (or a value that I put in it like 'All' or whatever) then to have the code go ahead and filter, then turn the filter back off only on that one column where it was getting the value from for that combo box. I have been working on this one little thing since 7:00 this morning and it's really really getting old. It's been 8 hours now, and the tables were already done!. I've searched all over the internet. All I want to do is open a table, have a filter applied to 2 columns, and turn one off if one of the combo boxes is blank.. that's it. I can't believe 1 little thing has to be this difficult. I appreciate your help and I will check out that video soon. Thanks!

[CJ_London]

tables are for storing data, not for display and manipulation. For that you use queries and forms. Which is why you appear to be struggling since the functionality you want is very limited in the table environment.

filters and criteria are two different things although they may appear to have the same effect.

if this was a query the criteria would be something like

"WHERE [Part_Type] = 'Plate' AND ([Material] = '" & me.somecontrol & "' OR me.somecontrol is null)"

but no idea how you would do that for a filter since you cannot reference outside of the table other than excluding it from your text

"[Part_Type] = 'Plate'"

I suggest you stop trying to swim against the tide and use the tools the way they are intended to be used. At the very least, use a query

[Micron]

Aside from the other good advice I'll answer this

Now I want to be able to turn only one of them off

so that you might also change your mindset about filters. You can have umpteen And pieces strung together in your filter expression and it is still only one filter. If you don't want even just one part for some case, it is a matter of reconstructing the new filter without that one And part. There is no such thing as turning on or of part of a filter. All of the components make up one filter.

[achammar]

Thank you for that! I actually wrote a query to do that. It worked good and Like it much better, but I couldn't figure out how to make it return records if the control it was getting it's criteria from had no value in it in which case I would want it to return all records for that field. I'm able to put the criteria in the query just fine and run it perfectly otherwise. I don't understand yet where to use the "WHERE" statement yet. I need to learn that better... but thank you very much! I know queries are much better.

[CJ_London]

I don't understand yet where to use the "WHERE" statement yet.

in the query builder, the WHERE part is the criteria section below the 'Show' line. So you have your table, on the field row select the fields you want to display. in the criteria section below your material field right click and select Build - navigate to your form and select the field you want it to equal. You should get something like

forms!myform!cboMaterial

now edit what you have just created and add the rest of the statement so you get

forms!myform!cboMaterial OR forms!myform!cboMaterial is null

click on the sql button (top left of ribbon or bottom right of query window) and you can see the query you have written in sql - you'll see the WHERE has been added automatically as well as table names, parentheses etc.

We communicate using sql because it is generally easier to read than a screenshot of the query window where names and/or columns may not be fully visible.

A tip for the future - explain what you are trying to, provide the actual sql as text (i.e. copy paste it), not a screenshot or a simplified view where you change names 'for confidentiality'. That way we can copy paste to provide the modified sql which all you then have to do is copy into the sql window then if you wish revert to query builder view.

Doesn't always work of course, typo's and the like, but overall it saves both you and us an awful lot of time.