Multiple search Criteria in vba

[ittechguy]

I have a problem here I was hoping someone could help me on.



I'm building a search form which contains several search boxes. Some of them are text, and some are numbers. I am successful in getting it to work with text (cboSearchLastName and cboSearchFirstName). However when I add numbers I remove the single quote and then it no longer works with "AND" at the end of it. It gives me run-time error 3075 and says invalid syntax "WHERE OrganizationFK = 1 AND". If I remove the AND it works without a problem, but then I cannot expand my search criteria.

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

What am I doing wrong?

[ittechguy]

One other question.

In many code examples (but not all) "sqlSearch" is included after the equals sign. Like this: sqlSearch = sqlSearch &. Usually it's called strWhere but name doesn't matter.

Why is that? What does that do? Why not leave it out and just have it like I did it above? Is it wrong to do it like that?

[pbaldy]

When you see it done like that they are building up the string step by step. When an optional search field has been filled in, you add it to the string. You add And between each, typically trimming off the trailing And at the end. Which is probably your solution.

[orange]

See Martin Green's site for vba and SQL info. There are 6 lessons, starting here.

[ittechguy]

I figured it out!

You were right pbaldy.

Also I found out I can't have each search string be its own SQL statement and then use that as the RecordSource. Because, there can only be one query as the recordsource for the form. So, I had to set the recordsource to my sql statement and then filter from there. Also I needed to write code to automatically remove the trailing " AND ".

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")
      '  MsgBox "No Records Found."
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

[pbaldy]

You were right pbaldy.

I'm always right...except when I'm wrong.

Glad you got it working.