Using AND between multiple search criteria

[ittechguy]

I have a problem with a search form I'm building. My search criteria works without a problem with text searches. But I added a couple numbers searches (cboSearchOrganization and cboSearchShopName). It seems my use/placement of AND is not correct. It is telling me invalid syntax.

Private Sub cmdSearch_Click()
Dim sqlSearch As String
     If Not IsNull(Me.cboSearchLastName) Then
          sqlSearch = "SELECT tblCustomer.LastName, tblCustomer.FirstName, tblCustomer.OrganizationFK," _
& " tblCustomer.ShopNameFK, tblCustomer.OfficeSymFK, tblFacilityMgr.CustomerFK, tblFacilityMgr.BuildingFK, 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" _
& " WHERE LastName ='" & Me.cboSearchLastName & "'" _
& " UNION SELECT tblCustomer.LastName, tblCustomer.FirstName, tblCustomer.OrganizationFK," _
& " tblCustomer.ShopNameFK, tblCustomer.OfficeSymFK, tblRoomsPOC.CustomerFK, tblRooms.BuildingFK, tblRoomsPOC.RoomsFK" _
& " FROM tblRooms INNER JOIN (tblCustomer INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK)" _
& " ON tblRooms.RoomsPK = tblRoomsPOC.RoomsFK" _
& " WHERE LastName ='" & Me.cboSearchLastName & "'AND"
   End If
   If Not IsNull(Me.cboSearchFirstName) Then
          sqlSearch = "SELECT tblCustomer.LastName, tblCustomer.FirstName, tblCustomer.OrganizationFK," _
& " tblCustomer.ShopNameFK, tblCustomer.OfficeSymFK, tblFacilityMgr.CustomerFK, tblFacilityMgr.BuildingFK, 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" _
& " WHERE FirstName ='" & Me.cboSearchFirstName & "'" _
& " UNION SELECT tblCustomer.LastName, tblCustomer.FirstName, tblCustomer.OrganizationFK," _
& " tblCustomer.ShopNameFK, tblCustomer.OfficeSymFK, tblRoomsPOC.CustomerFK, tblRooms.BuildingFK, tblRoomsPOC.RoomsFK" _
& " FROM tblRooms INNER JOIN (tblCustomer INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK)" _
& " ON tblRooms.RoomsPK = tblRoomsPOC.RoomsFK" _
& " WHERE FirstName ='" & Me.cboSearchFirstName & "'AND"
   End If
   If Not IsNull(Me.cboSearchOrganization) Then
          sqlSearch = "SELECT tblCustomer.LastName, tblCustomer.FirstName, tblCustomer.OrganizationFK," _
& " tblCustomer.ShopNameFK, tblCustomer.OfficeSymFK, tblFacilityMgr.CustomerFK, tblFacilityMgr.BuildingFK, 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" _
& " WHERE OrganizationFK =" & Me.cboSearchOrganization & "" _
& " UNION SELECT tblCustomer.LastName, tblCustomer.FirstName, tblCustomer.OrganizationFK," _
& " tblCustomer.ShopNameFK, tblCustomer.OfficeSymFK, tblRoomsPOC.CustomerFK, tblRooms.BuildingFK, tblRoomsPOC.RoomsFK" _
& " FROM tblRooms INNER JOIN (tblCustomer INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK)" _
& " ON tblRooms.RoomsPK = tblRoomsPOC.RoomsFK" _
& " WHERE OrganizationFK =" & Me.cboSearchOrganization & " AND "
   End If
   If Not IsNull(Me.cboSearchShopName) Then
   sqlSearch = "SELECT tblCustomer.LastName, tblCustomer.FirstName, tblCustomer.OrganizationFK," _
& " tblCustomer.ShopNameFK, tblCustomer.OfficeSymFK, tblFacilityMgr.CustomerFK, tblFacilityMgr.BuildingFK, 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" _
& " WHERE ShopNameFK =" & Me.cboSearchShopName & "" _
& " UNION SELECT tblCustomer.LastName, tblCustomer.FirstName, tblCustomer.OrganizationFK," _
& " tblCustomer.ShopNameFK, tblCustomer.OfficeSymFK, tblRoomsPOC.CustomerFK, tblRooms.BuildingFK, tblRoomsPOC.RoomsFK" _
& " FROM tblRooms INNER JOIN (tblCustomer INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK)" _
& " ON tblRooms.RoomsPK = tblRoomsPOC.RoomsFK" _
& " WHERE ShopNameFK =" & Me.cboSearchShopName & ""
End If
        Call MsgBox(sqlSearch, vbOKOnly, "Debug")
Me.RecordSource = sqlSearch
End Sub

If I delete the shopname string and just leave Organization, it works without a problem. It seems to me like my problem is with AND. What am I doing wrong?

[Stewart Ross]

You've said that the text searches work, but I'm not so sure that this is so. You are using separate IF statements to test your combos. Assume for the moment that just the first combo, cboSearchLastName, has a value, so the IF at line 3 is satisfied and none of the other IF statements is executed. By the end of the first set of SQL build statements at line 14 you have a dangling AND at the end of the Where clause:

" WHERE LastName ='" & Me.cboSearchLastName & "'AND"

The same applies to other IF statements at lines 27 and 40. SQL statements cannot end with an unterminated AND, so trying to use such statements as the recordsource of your form (line 56) is not going to work as it stands.

Can you explain more about why there is an AND at the end of the WHERE clause in three of the four tests, and what you expect to be ANDed in these cases?

-Stewart

[ittechguy]

Thanks for your reply.

I'm trying to build a search form which sets the record source for the form based on the search. There are several search criteria. I have last name, first name, organization, shop name, office symbol, etc. I need to be able to search through one or several. So Last Name, First Name, and Organization. Or just last name.

That's why I have AND at the end of the WHERE clause. I'm trying to get it to search through several if statements. I'm thinking, maybe I need to have sqlSearch = sqlSearch & at the beginning of each statement?

[ittechguy]

I just realized, I do not think the recordsource can be multiple queries. It seems perhaps I may have to have the recordsource be my main query (which contains everything), then filter it based on whats being searched for. Does that sound right?

[NeoPa]

You've made life very complicated for yourself. I'm not sure, but we may be going over ground here we've covered before. Certainly, the .RecordSource property is a more complicated, and unnecessarily so, approach when all you require is to filter your form. There is a .Filter property for doing just that. See Cascaded Form Filtering for everything you should need for filtering your forms.

NB. Multiple filtering ComboBoxes doesn't necessarily imply cascading. This is only true if the selected item in one determines the list to select from in any of the others. Often A determines B, determines C, etc. The logic flows like a waterfall from one through and past all the others.

In essence, you should build up a filter string by continuously adding strings that start with " AND ". When completed, you know you have an extraneous " AND " at the beginning of your filter string so you remove it. This is done very simply using :

Blahblah = Mid(YourString, 6)

[NeoPa]

I seem to have cross-posted with you.

ITTechGuy:
Does that sound right?

Absolutely right. Yes :-)

[Stewart Ross]

You are on the right track in thinking about your recordsource as your main query. You can either build the WHERE clause in VBA, or you can build a filter string and apply that to your form as a filter instead. Either way, you need to use correct syntax for your WHERE clause (and a filter string is just a WHERE clause without the WHERE keyword). That means you if you want to use the keyword AND to join sections of your clause together you must work out how to do so in a way that does not leave ANDs dangling. (Edit see the simple method for removing the last AND in a sequential build posted by NeoPa above)

Here's an example from one of my forms which has some optional and some linked filters for departments and tasks:

    strSQL = "SELECT DISTINCT " & strDateFieldName & " FROM " & Me.frmTaskSummary.Form.RecordSource & " WHERE "
    If strTaskDeptFilter = "" Then
        strSQL = strSQL & "TRUE"
    Else
        strSQL = strSQL & strTaskDeptFilter & " AND " & strPlanFilter
    End If
    If AdditionalWhereClause <> "" Then
        strSQL = strSQL & " AND " & AdditionalWhereClause
    End If

The first IF tests a filter string (set elsewhere) and appends the value TRUE to the WHERE of the SQL if the filter string is not set.

Edit
Sorry, cross-posted with NeoPa. Follow his advice and you won't go wrong!

-Stewart

[ittechguy]

Ok. I've rebuilt my code as follows:

Private Sub cmdSearch_Click()
Me.RecordSource = "SELECT tblCustomer.LastName, tblCustomer.FirstName, tblCustomer.OrganizationFK," _
& " tblCustomer.ShopNameFK, tblCustomer.OfficeSymFK, tblFacilityMgr.CustomerFK, tblFacilityMgr.BuildingFK, 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 tblCustomer.LastName, tblCustomer.FirstName, tblCustomer.OrganizationFK," _
& " tblCustomer.ShopNameFK, tblCustomer.OfficeSymFK, tblRoomsPOC.CustomerFK, tblRooms.BuildingFK, tblRoomsPOC.RoomsFK" _
& " FROM tblRooms INNER JOIN (tblCustomer INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK)" _
& " ON tblRooms.RoomsPK = tblRoomsPOC.RoomsFK"
Dim strWhere As String
     If Not IsNull(Me.cboSearchLastName) Then
          strWhere = strWhere & "[LastName] ='" & Me.cboSearchLastName & "'AND"
             End If
             If Not IsNull(Me.cboSearchFirstName) Then
          strWhere = strWhere & "[FirstName] ='" & Me.cboSearchFirstName & "'AND"
             End If
             If Not IsNull(Me.cboSearchOrganization) Then
          strWhere = strWhere & "[OrganizationFK] =" & Me.cboSearchOrganization & "AND"
             End If
             If Not IsNull(Me.cboSearchShopName) Then
          strWhere = strWhere & "[ShopNameFK] =" & Me.cboSearchShopName & "AND"
             End If
             If Not IsNull(Me.cboSearchOfficeSym) Then
          strWhere = strWhere & "[OfficeSymFK] =" & Me.cboSearchOfficeSym & ""
             End If
         Call MsgBox(strWhere, vbOKOnly, "Debug")
Me.Filter = strWhere
Me.FilterOn = True
End Sub

I added the criteria one if statement at a time to ensure it works properly. Everything worked (or so it seems) until I added cboSearchOfficeSym. Which then it fails telling me invalid syntax.

I double checked, choSeachOfficeSym and OfficeSymFK are both correct. I do not understand why cboSearchShopName is working but cboSearchOfficeSym is not.

I even entered the criteria in the query builder and it worked. This makes me think something else is wrong with my code.

[ittechguy]

I figured it out! I couldn't sleep (this project has been many long hours at work and sleepless nights) so I got up and worked on it again. I figured it wasn't right to manually remove the trailing " AND " because I do not know what order the user would search it from. So its impossible to know which AND to remove. So I wrote (or more accurately, copy /paste) some code to remove the " AND " automatically. Works like a charm!

Private Sub cmdSearch_Click()
Me.RecordSource = "SELECT tblCustomer.LastName, tblCustomer.FirstName, tblCustomer.OrganizationFK," _
& " tblCustomer.ShopNameFK, tblCustomer.OfficeSymFK, tblFacilityMgr.CustomerFK, tblFacilityMgr.BuildingFK, 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 tblCustomer.LastName, tblCustomer.FirstName, tblCustomer.OrganizationFK," _
& " tblCustomer.ShopNameFK, tblCustomer.OfficeSymFK, tblRoomsPOC.CustomerFK, tblRooms.BuildingFK, tblRoomsPOC.RoomsFK" _
& " FROM tblRooms INNER JOIN (tblCustomer INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK)" _
& " ON tblRooms.RoomsPK = tblRoomsPOC.RoomsFK"
Dim strWhere As String
Dim lngLen As Long
     If Not IsNull(Me.cboSearchLastName) Then
          strWhere = strWhere & "[LastName] ='" & Me.cboSearchLastName & "' AND "
             End If
             If Not IsNull(Me.cboSearchFirstName) Then
          strWhere = strWhere & "[FirstName] ='" & Me.cboSearchFirstName & "' AND "
             End If
             If Not IsNull(Me.cboSearchOrganization) Then
          strWhere = strWhere & "[OrganizationFK] =" & Me.cboSearchOrganization & " AND "
             End If
             If Not IsNull(Me.cboSearchShopName) Then
          strWhere = strWhere & "[ShopNameFK] =" & Me.cboSearchShopName & " AND "
             End If
             If Not IsNull(Me.cboSearchOfficeSym) Then
          strWhere = strWhere & "[OfficeSymFK] =" & Me.cboSearchOfficeSym & " AND "
             End If
             If Not IsNull(Me.cboSearchBuildingName) Then
          strWhere = strWhere & "[BuildingFK] =" & Me.cboSearchBuildingName & " AND "
             End If
        Call MsgBox(strWhere, vbOKOnly, "Debug")
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else
        strWhere = Left$(strWhere, lngLen)
        Call MsgBox(strWhere, vbOKOnly, "Debug")
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

One question though. If I search for something which brings no results (like say building "A", LastName "Jones" and Jones isn't a facility manager or room POC for building A), as it is now the form disappears as if by magic and I do not see anything until I search for something else.


How can I make it so that instead, it:

--Displays a message box which says something like "Sorry, there were no results for that search"
--Returns to a blank form after user hits "OK"

I feel like I should know this, it sounds simple enough. But perhaps my brain isn't functioning well at 1:00 in the morning.

I know I need to use something like this: MsgBox "No Records Found." But, I'm not sure how to let vba know that no results were found.

[ittechguy]

Forgot to attach image of form being filtered when there are no results for a search.

[ittechguy]

Did some research. it looks like I need to use DCount, like this;

If DCount("*", "yourQueryNameHere") = 0 Then

That won't work though because I don't have a query name per se. I have the SQL string at the beginning and then my where clauses.

[jforbes]

If your Form has the AllowAdditions properties set to False and then it is Filtered with a Filter that would return zero records in your result set, the Form goes into a weird state and pretty much all the controls will disappear. I wish it didn't but it does. It's as if the Form expects a current record, but there isn't one and it breaks the chain of events during the rendering of the Form.

What I've done to remedy this is to set AllowAdditions to True and then add the following code in a BeforeInsert Event to stop users from entering information on the NewRecord.

Private Sub Form_BeforeInsert(Cancel As Integer)
    msgBoxInfo ("Please use the New Quote Button from the Main Menu instead of entering a new record directly into this Form.")
    Cancel = True
End Sub

It's not the prettiest of solutions, but it works without too much fuss. Another option, that you seem to be pursuing, is to get a record count before applying a Filter. This a pretty good solution. The reasons I haven't used it:

• It would take longer to perform which might not be a big deal, but there would be two queries to make it work vs one.
• It wont catch the situation where the Form is opened with a Filter. Which is something that I seem to do often.

[ittechguy]

Thanks jforbes!

It is weird that the form does this. I think in my situation, it may be best to use DCount. I do not care so much about the slightly longer time to load, this is not going to be a huge database anyways.

How would I use DCount in this situation? I can't use "myQueryNameHere" because I don't have a query name. I have a search string, called strWhere.

[jforbes]

There are a few different ways to approach this, but the easiest would be to create a saved Query Definition based on what you have for your RecordSource in post #9 and use it as the basis of the DCount. You could also then base you Form on the Query. If you did this you would end up with something like this:

If DCount("LastName", "NameOfYourNewQuery", strWhere) > 0 Then
    ...Do Your Stuff...
End If

Here is Microsoft's info on Dcount:https://support.office.com/en-in/art...a-11a64acbf3d3

[NeoPa]

ITTechGuy:
How would I use DCount in this situation? I can't use "myQueryNameHere" because I don't have a query name. I have a search string, called strWhere.

Indeed. One of the limitations of the Domain Aggregate functions (All those beginning D...) is that they won't take a SQL string, but only an Access Object (such as TableDefs and QueryDefs). This is why JForbes suggests saving the logic currently held in your string variable into a QueryDef object.

Obviously, all the changeable items would be applied in the Criteria parameter with the QueryDef containing just the logic that's constant and common to all.

[ittechguy]

NeoPa,

I ended up using an actual query instead of having an SQL statement in my code. It works a lot better now. The only issue I'm having now is duplicate data in the RecordSource.

[NeoPa]

Do you have details of that for us, or do you plan to ask that as a separate thread?

[ittechguy]

I have another thread, here http://bytes.com/topic/access/answer...es-union-query

Its long, sorry. I hope it explains in enough detail the problem I'm having. Its been extremely frustrating. I've spent hours after hours searching everywhere online, I can't seem to find a solution to my problem.