Filtering Question

[dstrain]

I have a split form that I am using to filter through records with an unbound text box. I have a field called Project Type that I use to classify different projects. Some of the categories are Wind Damage, Hail Damage, Roof Damage, etc. My code for filtering is below

Private Sub Command34_Click()
Me.Filter = "[Project Type]Like '" & Me.Text32 & "*'"
Me.FilterOn = True
Me.Requery
End Sub
Private Sub Command35_Click()
Me.FilterOn = False
Me.Requery
End Sub

This code works just fine, but my question is if it is possible to add another field called Project Type 2 and filter by that also. To explain further, I can classify project types by more than one category, so I can have a project that can be Hail Damage and Wind Damage. Can I filter through two fields so if I am searching for Hail Damage a record with Hail Damage in the Project Type field will show and also a record with Hail Damage in Project Type 2 would show even though it has something different in the Project Type field. Any help is greatly appreciated.

 

[wazz]

how about:

Me.Filter = _
    "[Project Type] Like '" & Me.Text32 & "*'" Or _
    "[Project Type 2] Like '" & Me.Text32 & "*'"

i don't use filter much so i'm not sure if you can do And/Or with a filter but i bet you can.

by the way, your table should (could) be redesigned such that you move all the project's types to a separate table.

tblProject --> tblProjectTypes <-- tblCategories

each project can have many types/categories ([Project Type], [Project Type 2], etc.), so instead of creating a new field for every type, you would simply enter every project's type/category into a central table. in order to know which project they belong to, you add a second column in the table, ProjectID.

 

[dstrain]

Thanks for the suggestion. I tried using Or & AND but they both caem up with a runtime error 13 Type mismatch. I am just learning VBA so I'm not really sure what to look for to fix that. I will keep trying though. Does anyone have any other ideas??

Oh and for my tables, I do have a central table for Project Type. I have about 30 different types in there including things such as wind, hail, fire, water. I have two Project types on my form because one record can be classified as two different types, for example wind and hail. This is why I am trying to search the way I am. If one record has hail in project type and roof damage in project type 2, and another record has water in project type and hail in project type 2, I want to be able to have the form return both these records by searching for hail.

 

[wazz]

Thanks for the suggestion. I tried using Or & AND but they both caem up with a runtime error 13 Type mismatch.

a type mismatch would indicate that you are searching for one data type when the actual data is another. you are currently searching for strings. is that the actual data type or is it maybe number type?

Oh and for my tables, I do have a central table for Project Type. I have about 30 different types in there including things such as wind, hail, fire, water.

that's good.

I have two Project types on my form because one record can be classified as two different types, for example wind and hail.

that's not so good, if i understand. do you mean you have two Project types on your form or your table? (or both). it's good that you have a separate table for all the types but the types should not be stored in the project table, they should be stored in a third table (the central one in my diagram above).

anyway, i guess you need to confirm the data types first. your search is looking for strings; the quotes show that:
"[Project Type] Like '" & Me.Text32 & "*'"

are you storing strings or an ID? the ID might be a Long (number).

 

[dstrain]

I am storing the ID. I just have the code as it is because that was what I found online. How can I switch it to search for numbers?

 

[wazz]

try this:
Me.Filter = "[Project Type] = " & Me.Text32 & " Or [Project Type 2] = " & Me.Text32

 

[dstrain]

Thank You very much. That worked great. I just had to switch some things around with my table for that code.

 

[wazz]

that's great. tnx for replying.