Problems with AND / OR on multiple criteria search form.

[ittechguy]

Pretty sure this is a super simple fix, I'm probably too tired to see it.

I have a search form which uses the IN function to find values which are not in the original query for the form. Here is my code:

Private Sub cmdSearch_Click()Dim strStart 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 " _
    & " FROM tblCustomer INNER JOIN tblFacilityMgr ON tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK " _
    & " WHERE tblCustomer.LastName ='" & Me.cboSearchLastName & "') OR "
    strFilter = strFilter & "tblRooms.RoomsPK IN( " _
    & " SELECT tblRoomsPOC.RoomsFK " _
    & " 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 " _
    & " 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 " _
    & " FROM tblCustomer INNER JOIN tblFacilityMgr ON tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK " _
    & " WHERE tblCustomer.OrganizationFK =" & Me.cboSearchOrganization & ") OR "
    strFilter = strFilter & "tblRooms.RoomsPK IN( " _
    & " SELECT tblRoomsPOC.RoomsFK " _
    & " 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

I have two SQL statements for each combo box because I need it to search through both tblFacilityMgr and tblRoomsPOC. It compiles fine but it is not searching the way I want it to. I am pretty sure it is because I did something wrong with how I included " OR " between tblFacilityMgr and tblRoomsPOC. Whenever I complete a search, it seems to treat it as OR instead of AND. For an example, if I search for LastName "Jackson" and OrganizationFK "2" it will return records which match either Jackson or OrganizationFK 2. It should be Jackson AND organizationFK 2. Also If I search for LastName "Jackson", FirstName "James" it will return either. So if I have a lot of customers who's first name is "James" it will return every record.


Below is a screenshot of the filter string.



I really appreciate the help.

[CJ_London]

1. looking at your debug you have ' IN( SELECT' rather than ' IN (SELECT'
2. If tblCustomer, tblrooms etc are in your main query they need to be aliased otherwise the query engine cannot distinguish between the two - use tblCustomer AS T
3. if you are mixing AND and OR then then you need brackets - (A OR B) AND C AND D (think of it in a similar way you would multiplication and addition (A*B)+C+D produces a different result to A*B+C+D or A*(B+C+D) - at the end of the day you are adding 1's and 0's, true and false) Not sure where the brackets go in your case, depends on what you are trying to achieve
4. You seem to have a very complex way of solving a simple problem, perhaps providing your table schema/relationships and a description of what you are trying to do would result in a much simpler outcome

[ittechguy]

Thanks for your reply!

1. I did not know that mattered, I changed that.

2. tblCustomer is not in the main query. tblRooms is. I guess that means I have to use an Alias. Below is the SQl for the main query:

SELECT tblBuilding.BuildingPK, tblBuilding.BuildingName, tblRooms.RoomsPK, tblRooms.RoomName
FROM tblBuilding 
INNER JOIN tblRooms ON tblBuilding.BuildingPK = tblRooms.BuildingFK;

3. That makes a lot of sense. I'm not sure where the brackets should go either, but I've been playing with it.

4. I've been working on this for over 2 months now. My knowledge of access is limited, but slowly growing. The way I'm doing it now, is far simpler than what I previously did. Before this I was using a union query, but I couldn't get around duplicate data in the RecordSource.

What I'm trying to do is simple, but it seems the solution complicated. I want to filter by customer's information, room information, building information, and equipment information. Most of which is not located in the main query. It should return a Building ID or Room ID. So I am using IN() to select values located in other tables. If there is an easier way to do this, I am all ears.

Here is a screenshot of my relationships.

[orange]

Here is a link describing BEDMAS --order of operations. Very important with And, OR, divide etc.

[CJ_London]

Below is the SQl for the main query:

but your form is not displaying any data from the query - it has subforms, each with it's own recordsource

I'm not sure where the brackets should go either, but I've been playing with it.

create a temporary query to build your ands and ors then look at the sql generated - you may end up with something like

(A OR B) AND (A OR C) AND (A OR D)


Re your db design, not sure why you have a customerFK in tblFacilityManager - you would get to this via the room and building. At the moment you are creating a relationship 'loop' which could be stopping the db from displaying the data. For now recommend remove the relationship between tblCustomer an tblFacilityManager

I cannot see sufficient of your form to advise further but suspect what you want won't work the way you expect. It looks like you have a subform for buildings and one for rooms and the idea is to display all filtered buildings and all filtered rooms - but how will you tell which room belongs to which building?

But this is my suggestion:

Everything you are searching for is customer related - so your main form needs to bring back a customerFK

Each customer can have many roomPOCs so your room subform would have a recordsource of a combination of tblRooms and tblRoomsPOC (i.e. SELECT * FROM tblRooms INNER JOIN tblRoomsPOC on tblRooms.RoomsPK=tblRoomsPOC.RoomsFK). Then you would set the subform linkchild property to CustomerFK and the linkmaster property to CustomerPK

Now for buildings - each room can only have one building and there is only one field so you can either add buildings into the above recordsource or if you want it in a separate subform just have tblBuildings as a recordsouce (or add the facilitiesmanafer table) and set the linkchild property to BuildingPK and the linkmaster to subformname.form.buildingFK where subformname is the name of your room subform. As you click through the different rooms, the related building will display


Now to a potential problem - the customerFK that is brought back - there could be more than one and the above will only work on the first customerfk found - to see the next one, you need to go to the next record.

So the solution is to have another subform to display the results of your search and make your main form unbound (no recordsource). You would then modify your rooms subfom linkmaster property to be subformname.form.customerPK - as you click through the different customers the rooms and related building subforms will refresh automatically.

[ittechguy]

Thank you for your reply Ajax.

I'm sorry if I didn't explain the form well enough.

The main form contains Building ID, Room ID, Building Name, and Room Name. I have 3 subforms which display information related to Building ID and Room ID. Facility Manager, Room Point of Contacts, and Equipment. Facility Manager subform displays customer information from tblCustomers for all the facility managers for the building which is displayed in the main form. Room POC Subform displays customer information from tblCustomers for all the points of contact for the room which is displayed in the main form. Equipment subform displays information about equipment which is located in the room which is displayed in the main form. I haven't yet build the equipment subform. I'm sorry, it wasn't very clear from that screenshot.

I need to be able to display Building Name and Room name in the main form. Then all related records are displayed in subforms. I may change these to List boxes, not entirely sure at this point.

tblFacilityMgr and tblRoomsPOC are junction tables. I need two separate tables because I need to distinguish between a Building Facility Manager, and a Room point of contact. One person is the POC for the entire building, the other is the POC for a room.

As per your suggestion, building and customer information cannot be in the same query as the RecordSource for the main form. If I do have it in the recordsource, there will be duplicate records because one customer can own many rooms/buildings and one room/building can be owned by many customers. Thats why I am using IN (). Customer information and equipment information is not in the main query so I'm trying to match the Building ID or the Room ID to fields in other tables.

All that said, do you think I'm on the right path by using IN ()? I'm trying to get the brackets right but its just not working out.

[CJ_London]

Sorry to be picky but you refer to ID's when your tables use PK/FK - I presume these are PK's?

If so, then I presume the recordsouce for the mainform is

SELECT roomsPK, BuildingFK, RoomName, BuildingName 
FROM tblRooms INNER JOIN tblBuilding on tblrooms.BuildingFK=tblBuilding.BuildingPK

And basically that will produce a (potentially) multiple row result based on a customer search filter from your five unbound combo's?

I also presume each of your combos rowsource include the PK as the bound source e.g.

SELECT *
FROM tblOrganisation

SELECT CustomerPK, FirstName
FROM tblCustomers

etc

If this is the case I would modify your code to

SELECT roomsPK, BuildingFK, RoomName, BuildingName, tblCustomers.*
FROM ((tblRooms INNER JOIN tblBuilding on tblrooms.BuildingFK=tblBuilding.BuildingPK) INNER JOIN tblRoomsPOC ON tblRooms.PK=thbRoomsPOC.RoomsFK) INNER JOIN tblCustomer ON tblCustomer.CustomerPK=tblRoomPOC.CustomerFK

then your vba code would become

me.filter="CustomerFK=" & cboFirstName & " OR CustomerFK=" & cboLastName & " OR OrganisationFK=" & cboOrganisation & " OR .....
me.filteron=true

I need to be able to display Building Name and Room name in the main form

As explained before and as you recognise, if a customer has more than one room then this will be a multiple return - you can only display one room per page so will need to go to next record to see the next room - you can't use subforms in a continuous form.

Whether you go for my simplified suggestion or stick with yours, the result is the same - multiple returns. You understand this but the filtering will not reduce this to a single record unless you also include a filter for the roomPK - hence my suggestion in the previous post which is to make the main form unbound and have another subform to display the records instead.

[ittechguy]

Thanks for the reply Ajax. Sorry for the confusion w/ IDs and PK/FK.

I now understand what you mean by simplifying the search form. That does make sense. At this point what I have is working so I don't see the point in changing it, but I've saved this post and I'll go back to it if I need to in the future. Definitely appreciate the help.