Filter set to multiple variables and/or multiple queries for one filter?

[ittechguy]

I have a problem I've been trying to fix for the past two weeks.

I'm trying to create a search form which filters :

--Customers first name, last name, organization, shop name, office symbol

--Building Name, Room Name

--Equipment name, equipment serial number, equipment IP address

Each search box is a combo box. A user can filter by one combo box, or by several. So there is " AND " between each if statement.

At first I used a union query and set that query as the recordsource for the form. I needed it to be a union query due to the fact that customers could be a building POC or a room POC and I need to search through both. Also I need it to be an actual query (as opposed to a simple SQL statement in VBA code) because I need to use DCount to count records and tell the user if there are no results.

The problem w/ using one main query is that there are duplicate results in the form recordsourse. Like if Michael Smith, Andy Jones, and John Doe own BuildingFK 1, RoomsPK 10, if I filter by BuildingFK 1, RoomsPK 10 I will see 3 records.

Now, the purpose of this search form is to filter by all those search boxes and return ONLY Building ID and Room ID. I have list boxes which populate data based on those two text boxes.

After much research, I've determined it must not be possible to filter out (filter out, not delete from table) duplicates in the form recordsource. It seems I am going to need to instead either clean up my query and/or use several smaller queries as my filter string instead of one super large one.

I've tried using several queries instead of one large one, but it would only use one of them as the filter string, not all of them. I need to figure out how to return building ID and room ID either using several different queries, or maybe by having different variables as Me.Filter =.

Another idea I had was to use different variables for Me.Filter and then use different queries. Ex.

if cboSearchLastName is notNull then Me.Filter = strFilter1 else if cboSearchFirstName is notNull then Me.Filter = strFilter2 End If

But I do not imagine this to work.

[jforbes]

ittechguy,

Based on the Schema and information from another of your posts http://bytes.com/topic/access/answer...es-union-query, I'll tell you what I would do to get things working smoothly, but I have a feeling that you won't like the recommendations.

First, before delving into the design, a few basics. It's easiest to use Access by letting Access do things the way it likes to do things. Things that Access likes to do:

• A Single updateable RecordSet for a Main Form or Stand-alone Form's RecordSource.
• A Single updateable RecordSet per SubForm OR a non-updatable Query if information is pulled from multiple sources.
• To display information from a related table, use a SubForm and let Access main the linkage between the Form and SubForm. This may have to be manually setup at design time, but let Access select the records at runtime.
• To restrict the amount of records being displayed for a Main Form, use a Filter. Access will then automatically select the records for any SubForms.

These aren't hard and fast rules, just things that make it much easier to use Access.

Your current Filter/Main Form deviates from this right away with the Query including Unions as the RecordSource for the Form which pulls in everything an then attempts to whittle it down based on the user filtering.

I think you would be better off creating a Main Form based on tblRoom (or tblBuilding), then creating SubForms to show the rest of the information related to the Room (Building), like the Building (Rooms) and People in those Rooms. Doing this, will make it so that all you have to do is Filter for the Room and all the SubForms will populate automatically.

Then the tricky part would be to determine which Buildings to include in the Filter based on what the user has supplied. I remember seeing a post related to this a few weeks ago where someone was recommending you use a IN() to Filter the Main Form. Their recommendation is based on having the Forms structured in the manner that Access likes, and it is still a valid recommendation. An example Query (not a Form Filter) using this technique from a project I've been working on looks like this:

SELECT 
  Quote.QuoteNumber
, Quote.Company
, Quote.QuoteDescription
FROM Quote
WHERE Quote.QuoteNumber IN (
   SELECT LineItem.DocNumber
   FROM LineItem
   WHERE LineItem.Description Like '*test*') 
OR Quote.QuoteNumber IN (
   SELECT Jobs.QuoteNumber 
   FROM Jobs
   WHERE Jobs.Description Like '*test*')

I've made it a little more readable, but for this example, the User enters "test" into a TextBox and the following SQL is generated to get all the Quotes where for any related table, the description contains "Test". For your task, it would be to generate the WHERE clause to apply to the Main Form, then all the SubForms would all be automatically populated.

So from some of the VBA you have supplied all ready, you can generate some code like this that you can use to Filter your Main Form:

' First Name
strWhere = strWhere & "tblRooms.RoomsPK IN( "
strWhere = strWhere & "SELECT tblRoomsPOC.RoomsFK "
strWhere = strWhere & "FROM tblRoomsPOC "
strWhere = strWhere & "INNER JOIN tblCustomer "
strWhere = strWhere & "ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK "
strWhere = strWhere & "WHERE tblCustomer.FirstNameLIKE '*" & Me.cboSearchFirstName & "*' ) AND "
 
' Last Name
strWhere = strWhere & "tblRooms.RoomsPK IN( "
strWhere = strWhere & "SELECT tblRoomsPOC.RoomsFK "
strWhere = strWhere & "FROM tblRoomsPOC "
strWhere = strWhere & "INNER JOIN tblCustomer "
strWhere = strWhere & "ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK "
strWhere = strWhere & "WHERE tblCustomer.LastName LIKE '*" & Me.cboSearchLastName & "*' )  AND "
 
' Building
strWhere = strWhere & "tblRooms.RoomsPK IN( "
strWhere = strWhere & "SELECT tblRooms.RoomsPK "
strWhere = strWhere & "FROM tblRooms "
strWhere = strWhere & "INNER JOIN tblBuilding "
strWhere = strWhere & "ON tblBuilding.BuildingPK = tblRooms.BuildingFK "
strWhere = strWhere & "WHERE tblBuilding.BuildingFK LIKE '*" & Me.cboSearchBuildingName & "*' ) AND "
 

This code is pretty basic and most likely has some bugs in it. It also doesn't address if the user hasn't supplied a value, which could be taken care of by some if statements. Anyway, hopefully it gives you an understanding of another approach to what you are trying to accomplish.

Lastly, this approach would mean that only one Room or Building would be on the screen at a time. With a little tweaking, this could display a list of Rooms that fit the criteria and then shows more information when a specific Room from the list is clicked on, by making the Form Continuous and putting the SubForms in the Footer of the Page.

One last note. By doing it this way, the data most likely can be updated from these Forms, which might be handy.

[ittechguy]

Thank you for your reply jforbes. Its not that I don't like your recommendations, I really appreciate them. But I'd be lying if I said I wasn't frustrated. I've been working on this project for nearly 3 months now. I've asked help in another forum, but I think they didn't really understand what I was trying to do.

Originally, I set up my form like you're describing using main forms and subforms. The main form was based on a table. I was told that I should use list boxes instead of subforms. I could not figure out how to filter the main form based on a value which wasn't located in the form's recordsource.

So, closest I've come to finishing this form was to use the ridiculously long union query. My recent idea was to use a concatenation function to concatenate the fields in the union query, which is even more ridiculous.

Anyways. Deep breaths for me, then I'm deleting the entire form and query and starting all over, yet again.

I do appreciate your help though. At least now I have a small amount of sanity to cling onto.

[jforbes]

Hang in there. What you are attempting to do is on of the more complicated scenarios for Access. I think the IN() operator will be key for you as it will allow you to Filter a Form on values that aren't included in the Form's RecordSource.

[ittechguy]

I just tried doing it like you said, using SQL in the filter variable. Its working perfectly! No more duplicate data and it just works.

Problem though. And its probably a simple one. I need to search through both tblRoomsPOC and tblFacilityMgr. Or, more accurately, I think it would be OR. That is, it should return a result if found in either tables.

This is my code here:

If Not IsNullOrEmpty(Me.cboSearchLastName) Then
startStr = IIf(strFilter = "", "", " AND ")
strFilter = strFilter & "tblRooms.RoomsPK IN( "
strFilter = strFilter & "SELECT tblRoomsPOC.RoomsFK, tblFacilityMgr.BuildingFK, tblCustomer.CustomerPK "
strFilter = strFilter & "FROM (tblCustomer INNER JOIN tblFacilityMgr ON tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK) "
strFilter = strFilter & "INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK "
strFilter = strFilter & "WHERE tblCustomer.LastName ='" & Me.cboSearchLastName & "')"
End If

Something is not right w/ the query. Its telling me that would produce multiple fields and I need to re-write the FROM statement. I really don't want to write another union query.

[ittechguy]

I thought I figured it out, but not just yet. I found out I was only supposed to select RoomsPK, and nothing else.

This is what I have now.

If Not IsNullOrEmpty(Me.cboSearchLastName) Then
startStr = IIf(strFilter = "", "", " AND ")
strFilter = strFilter & "tblRooms.RoomsPK IN( " _
& " SELECT tblRoomsPOC.RoomsFK " _
& " FROM (tblCustomer INNER JOIN tblFacilityMgr ON tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK) " _
& " INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK " _
& " WHERE tblCustomer.LastName ='" & Me.cboSearchLastName & "')"
End If

That compiles and seems to work great, but after some troubleshooting, I found out that it only works if a record is in tblRoomsPOC. If a record is in tblFacilityMgr but not in tblRoomsPOC, it will not display any results.

What have I don'e wrong w/ the query? I'm guessing its because I'm trying to also select tblRooms.RoomsPK and tblFacilityMgr.BuildingFK from two different tables. It seems to me like BuildingFK needs to also be before IN. Currently tblFacilityMgr.BuildingFK isn't even in the query for the main form.

Also, for some reason it doesn't like it if I select any more than 1 field in the sub query (after IN( ).

[ittechguy]

After much more research, I figured I need to use a union query after all, so i came up with this:

startStr = IIf(strFilter = "", "", " AND ")
strFilter = strFilter & "tblRooms.RoomsPK IN( " _
& " SELECT tblRooms.RoomsPK " _
& " FROM (tblBuilding INNER JOIN tblRooms ON tblBuilding.BuildingPK = tblRooms.BuildingFK) INNER JOIN (tblCustomer INNER JOIN tblFacilityMgr " _
& " ON tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK) ON " _
& " tblBuilding.BuildingPK = tblFacilityMgr.BuildingFK" _
& " UNION SELECT tblRooms.RoomsPK " _
& " FROM tblBuilding INNER JOIN (tblRooms INNER JOIN (tblCustomer INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK " _
& " = tblRoomsPOC.CustomerFK) ON tblRooms.RoomsPK = tblRoomsPOC.RoomsFK) ON tblBuilding.BuildingPK = tblRooms.BuildingFK " _
& " WHERE tblCustomer.LastName ='" & Me.cboSearchLastName & "')"
End If

It works in the access query builder. I've tested each half separately in VBA, no problem. When running the entire union query, it says that operator is not supported. Which has me thinking VBA doesn't support union queries in sub queries (?).

[jforbes]

Sounds like you are making progress.

I want to caution you from using Unions. They have their place, but I don't believe what you are doing would need one. Just use multiple IN() statements.

I reworked your code a little. I'm not comfortable with the way the joins are written as it makes it very difficult to read. Typically when writing a Select, the select is from the Table that holds the value that is needed and then Joins are added to the point that the criteria can be applied. Basically, walking a tree to the point where the magic criteria shows up. So, I reworked those Joins as well. Hopefully I didn't mess it up so much that you can't correct any errors:

If Len(Me.cboSearchLastName) > 0 Then
   strFilter = strFilter & " AND tblRooms.RoomsPK IN( " 
   strFilter = strFilter & " SELECT tblRooms.RoomsPK "
   strFilter = strFilter & " FROM tblRooms "
   strFilter = strFilter & " INNER JOIN tblBuilding ON tblBuilding.BuildingPK = tblRooms.BuildingFK "
   strFilter = strFilter & " INNER JOIN tblFacilityMgr ON tblBuilding.BuildingPK = tblFacilityMgr.BuildingFK "
   strFilter = strFilter & " INNER JOIN tblCustomer ON tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK "
   strFilter = strFilter & " WHERE tblCustomer.LastName ='" & Me.cboSearchLastName & "') "
End If
If Len(Me.cboSearchLastName) > 0 Then
   strFilter = strFilter & " AND tblRooms.RoomsPK IN( " 
   strFilter = strFilter & " SELECT tblRooms.RoomsPK "
   strFilter = strFilter & " FROM tblRooms "
   strFilter = strFilter & " INNER JOIN tblRoomsPOC ON tblRooms.RoomsPK = tblRoomsPOC.RoomsFK "
   strFilter = strFilter & " INNER JOIN tblCustomer ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK "
   strFilter = strFilter & " WHERE tblCustomer.LastName ='" & Me.cboSearchLastName & "') "
End If
' ... Add as many IN() statements as needed
If Len(strFilter) > 5 Then strFilter = Right(strFilter, Len(strFilter)-5)

Also, it may be beneficial to read through this a little https://msdn.microsoft.com/en-us/library/ms177682.aspx. It should help with understanding what the IN() is all about and why you got an error when the Select returned two columns of data.

[ittechguy]

Thanks for the help! I definitely agree w/ you on unions.

My project is coming together thanks to you, but I still have a kink to work out.

I've tried using multiple If statements so that each combo box would have two IFs. It was not working correctly because it was putting " AND " between the IF for tblFacilityMgr and the IF for tblRoomsPOC. This needs to be " OR ".

Reason for this is I want to return tblBuilding.BuildingPK if there is a record in tblFacilityMgr for the criteria I've entered. If there is no record in tblFacilityMgr, I need it to search in tblRoomsPOC. As it is now, it is only returning records if a customer's Last Name, first name, etc is in both tables.

So I've changed it to " OR ". Now the problem I'm having is it seems to be treating the ANDs like ORs. If I try to filter by LastName "Smith" and FirstName "Michael" it will show me all the records with lastname "Smith" and all the records with a first name "Michael".

Below is my code. I think I need to be using brackets to separate the OR so that its not being confused w/ AND. But I can't seem to get the placement right, or I am just completely wrong.

Private Sub cmdSearch_Click()
Dim startStr As String
Dim strFilter As String
If Not IsNullOrEmpty(Me.cboSearchLastName) Then
    startStr = IIf(strFilter = "", "", " AND ")
    strFilter = strFilter & startStr & "tblBuilding.BuildingPK IN (" _
    & " SELECT tblFacilityMgr.BuildingFK AS B " _
    & " FROM tblCustomer INNER JOIN tblFacilityMgr ON tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK " _
    & " WHERE tblCustomer.LastName ='" & Me.cboSearchLastName & "') OR "
    strFilter = strFilter & "tblRooms.RoomsPK IN (" _
    & " SELECT tblRoomsPOC.RoomsFK AS R " _
    & " FROM tblCustomer INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK " _
    & " WHERE tblCustomer.LastName ='" & Me.cboSearchLastName & "') "
End If
If Not IsNullOrEmpty(Me.cboSearchFirstName) Then
    startStr = IIf(strFilter = "", "", " AND ")
    strFilter = strFilter & startStr & "tblBuilding.BuildingPK IN (" _
    & " SELECT tblFacilityMgr.BuildingFK AS B " _
    & " FROM tblCustomer INNER JOIN tblFacilityMgr ON tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK " _
    & " WHERE tblCustomer.FirstName ='" & Me.cboSearchFirstName & "') OR "
    strFilter = strFilter & "tblRooms.RoomsPK IN (" _
    & " SELECT tblRoomsPOC.RoomsFK " _
    & " FROM tblCustomer INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK " _
    & " WHERE tblCustomer.FirstName ='" & Me.cboSearchFirstName & "') "
End If
If Not IsNullOrEmpty(Me.cboSearchOrganization) Then
    startStr = IIf(strFilter = "", "", " AND ")
    strFilter = strFilter & startStr & "tblBuilding.BuildingPK IN (" _
    & " SELECT tblFacilityMgr.BuildingFK AS B " _
    & " FROM tblCustomer INNER JOIN tblFacilityMgr ON tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK " _
    & " WHERE tblCustomer.OrganizationFK =" & Me.cboSearchOrganization & ") OR "
    strFilter = strFilter & "tblRooms.RoomsPK IN (" _
    & " SELECT tblRoomsPOC.RoomsFK AS R " _
    & " FROM tblCustomer INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK " _
    & " WHERE tblCustomer.OrganizationFK =" & Me.cboSearchOrganization & ") "
End If
 
 
Call MsgBox(strFilter, vbOKOnly, "Debug")
If DCount("*", "qryFrmMainRooms", strFilter) = 0 Then
                MsgBox "No corresponding records to your search criteria." & vbCrLf & vbCrLf
                Me.FilterOn = False
                Me.cboSearchLastName = ""
                Me.cboSearchFirstName = ""
End If
Me.Filter = strFilter
Me.FilterOn = True
End Sub

[jforbes]

I think this is what you are looking for:

...
If Not IsNullOrEmpty(Me.cboSearchLastName) Then
    startStr = IIf(strFilter = "", "", " AND ")
    strFilter = strFilter & startStr & "(tblBuilding.BuildingPK IN (" _
    & " SELECT tblFacilityMgr.BuildingFK AS B " _
    & " FROM tblCustomer INNER JOIN tblFacilityMgr ON tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK " _
    & " WHERE tblCustomer.LastName ='" & Me.cboSearchLastName & "') OR "
    strFilter = strFilter & "tblRooms.RoomsPK IN (" _
    & " SELECT tblRoomsPOC.RoomsFK AS R " _
    & " FROM tblCustomer INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK " _
    & " WHERE tblCustomer.LastName ='" & Me.cboSearchLastName & "')) "
End If
If Not IsNullOrEmpty(Me.cboSearchFirstName) Then
    startStr = IIf(strFilter = "", "", " AND ")
    strFilter = strFilter & startStr & "(tblBuilding.BuildingPK IN (" _
    & " SELECT tblFacilityMgr.BuildingFK AS B " _
    & " FROM tblCustomer INNER JOIN tblFacilityMgr ON tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK " _
    & " WHERE tblCustomer.FirstName ='" & Me.cboSearchFirstName & "') OR "
    strFilter = strFilter & "tblRooms.RoomsPK IN (" _
    & " SELECT tblRoomsPOC.RoomsFK " _
    & " FROM tblCustomer INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK " _
    & " WHERE tblCustomer.FirstName ='" & Me.cboSearchFirstName & "')) "
End If
If Not IsNullOrEmpty(Me.cboSearchOrganization) Then
    startStr = IIf(strFilter = "", "", " AND ")
    strFilter = strFilter & startStr & "(tblBuilding.BuildingPK IN (" _
    & " SELECT tblFacilityMgr.BuildingFK AS B " _
    & " FROM tblCustomer INNER JOIN tblFacilityMgr ON tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK " _
    & " WHERE tblCustomer.OrganizationFK =" & Me.cboSearchOrganization & ") OR "
    strFilter = strFilter & "tblRooms.RoomsPK IN (" _
    & " SELECT tblRoomsPOC.RoomsFK AS R " _
    & " FROM tblCustomer INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK " _
    & " WHERE tblCustomer.OrganizationFK =" & Me.cboSearchOrganization & ")) "
End If
... 

You should end up with something like ((In tblFacilityMgr) OR (tblRoomsPOC)) AND ((tblFacilityMgr) OR (tblRoomsPOC))

There is another option you may want to consider. You could replace all the ANDs with ORs. This is more inline with what I do. You'll typically get more results back but that usually isn't a problem if what the user types in is specific enough. If it is specific enough, they will be able to quickly find what they want in the results.
Actually, for most filtering, I use only one TextBox to get the criteria and then I look for it an any Key Field and then OR the results together and show it to the user. If the user puts in something non specific, like an "A" they will get about half of the database returned to them, but if they put in a partial key, they will usually get just enough records back that they can quickly find what they want.

You might want to try ORing the results together and see how your application responds. It's a little counter intuitive, but I think you will like it.