Buggy code (Missing Operator in Query Expression)

[ittechguy]

I have a search form which is working well. It does what I need it to do, until after several searches for no apparent reason, it stops working. It gives me a run-time error 3075 and says "Missing operator in query expression." I've tried a lot of things to see if the error will stop but nothing works. The only thing that has helped is to close out the form, re-open it, and then all is good... for another 15 minutes.

Here is my code. I'd really appreciate any help I can get.

Private Sub cmdSearch_Click()
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
             If Not IsNull(Me.cboSearchRoomName) Then
          strWhere = strWhere & "[RoomsPK] =" & Me.cboSearchRoomName & " 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."
      Dim Msg As VbMsgBoxResult

If DCount("*", "qryRecordSet", strWhere) = 0 Then
 Msg = MsgBox("No corresponding records to your search criteria." & vbCrLf & vbCrLf & _
    "Okay", vbCritical + vbYesNo)
  If Msg = vbYes Then
 Me.FilterOn = False
 Me.cboSearchBuildingName = ""
Me.cboSearchRoomName = ""
Me.cboSearchOrganization = ""
Me.cboSearchShopName = ""
Me.cboSearchOfficeSym = ""
Me.cboSearchLastName = ""
Me.cboSearchFirstName = ""
  Else
Me.Filter = strWhere
Me.FilterOn = True
  End If
 End If

[Seth Schrock]

In line 34, place

Debug.Print strWhere

This will output your string to the immediate window (Ctrl + G to view it). When it doesn't work, look at that and post it here so that we can see what went wrong.

[ittechguy]

Thanks for your help. I solved the problem. It was an issue with how I was using DCount to check to see if no records were found.