Access Access Eliminate Blank Query Results

[DK88]

I have searched around the internet for quite some time, and even used a couple other forum sites, but so far have found no solution for this problem. I would greatly appreciate anyone willing to take their time to try and figure this out. Thanks. This post will probably be a little lengthy, but I am trying to be as clear as possible, as the problem is a little confusing

I have a Microsoft Office database that is fairly simple. There is one talbe with five columns: Project #, Contract #, Date, Keyword, and Conversation. There is a form that contains this data, so the users don't have to mess with the raw table. There is a query from this table, and then a search form tied to this query. The goal of the database is to allow users to enter search criteria about the table and see the results that matched what they searched.

So for example, the user could search for a Project # of 1 a Contract # of 2, and leave the other three sections blank, and the query would yield all of the data entries that have a Project # of 1 and a Contract # of 2.

Using a system of "Is Null"s, I was able to get it so that the search query would only take the sections the user inputs into account. So in the above example, since the user only entered criteria into the Project # and Contract # columns, the keyword and date (conversation isn't part of the search) wouldn't be taken into account for the query, but would still be a part of the search results in each respective row.

This all works fine and dandy, as needed. When the user enters search criteria into any or all of the columns, the query yields the results that match that critera. Here's where the problem comes though:

Whenever a user inputs partially blank entries (which will happen with this database), the query doesn't work. So let's say a user inputs a brand new entry into the table, with Project # of 3, a date of 1/1/14, a keyword of Pineapple, and some irrelevant conversation, but they left the Contract # section blank. If you were to then run a search for any entries that have a Contract # of 7, this result shouldn't be included in the results, as it doesn't contain a Contract #. But since the section was left blank, it counts as a positive result. Furthermore, if you search a date of 9/9/19, this result would also be counted as a positive result (even though the date doesn't match at all) because the Contract # column was left blank.

I hope that makes sense, as it is a little confusing. If you have any questions, please ask.
The SQL of the current database is as follows:

SELECT Conversations.[Project #:], Conversations.[Contract #:], Conversations.[Date:], Conversations.[Keyword:], Conversations.[Conversation:]
FROM Conversations
WHERE (((Conversations.[Project #:])=[Forms]![Conversation Search Form]![Project #]) AND ((Conversations.[Contract #:])=[Forms]![Conversation Search Form]![Contract #]) AND ((Conversations.[Date:]) Between Nz([Forms]![Conversation Search Form]![Start Date],#1/1/1900#) And Nz([Forms]![Conversation Search Form]![End Date],#9/9/2999#)) AND ((Conversations.[Keyword:]) Like "*" & [Forms]![Conversation Search Form]![Keyword] & "*")) OR (((Conversations.[Contract #:])=[Forms]![Conversation Search Form]![Contract #]) AND ((Conversations.[Date:]) Between Nz([Forms]![Conversation Search Form]![Start Date],#1/1/1900#) And Nz([Forms]![Conversation Search Form]![End Date],#9/9/2999#)) AND ((Conversations.[Keyword:]) Like "*" & [Forms]![Conversation Search Form]![Keyword] & "*") AND ((([Conversations].[Project #:]) Like [Forms]![Conversation Search Form]![Project #]) Is Null))

This is only the first two iterations of the code: the entire SQL is too long to post. The first iteration of this is the situation in which the user inputs search criteria into all 5 sections. The second situation is where the user inputs search criteria into 4 sections, but leaves the Project # blank. As you can see by the code, the Project # becomes Null, meaning it won't affect the search.

I recognize that the title headings I chose could potentially be a problem (I.E. Using built in function words like "Date" and having spaces and # in the titles. As part of the troubleshooting for this problem in a different forum, I changed all of the names and syntax to more appropriate names, but it still didn't help fix the problem in any way. The person helping me on the other site recommending using VBA code in the form instead of SQL in the query, as SQL can apparently have issues with larger databses. We have gone through quite a bit of tweaking and working on VBA code, and have come up with what SHOULD work, but doesn't. I will also post that code below (either option would suffice for me, as long as we can fix the problem!):

Private Sub txtProject_AfterUpdate()
    ApplyFilters
End Sub
Private Sub txtStartDate_AfterUpdate()
    ApplyFilters
End Sub
Private Sub txtEndDate_AfterUpdate()
    ApplyFilters
End Sub
Private Sub txtKeyword_AfterUpdate()
    ApplyFilters
End Sub
Private Sub ApplyFilters()
On Error GoTo EH
    Dim strFilter As String
    strFilter = ""
 
    'Cover all the possible cases:
 
    'First check the Project #
    If Not IsNull(Me.txtProject) Then
        If strFilter <> "" Then
            strFilter = strFilter & _
                " AND Project = " & Me.txtProject
        Else
            strFilter = strFilter & _
                "Project = " & Me.txtProject
        End If
    End If
 
    'Check the Contract #
    If Not IsNull(Me.txtContract) Then
        If strFilter <> "" Then
            strFilter = strFilter & _
                " AND Contract = " & Me.txtContract
        Else
            strFilter = strFilter & _
                    "Contract = " & Me.txtContract
        End If
    End If
 
 
    'Check the Starting Contract Date
    If Not IsNull(Me.txtStartDate) Then
        If strFilter <> "" Then
            strFilter = strFilter & _
                " AND ConversationDate >= #" & Me.txtStartDate & "#"
        Else
            strFilter = strFilter & _
                "ConversationDate >= #" & Me.txtStartDate & "#"
        End If
    End If
 
    'Check the Ending Contract Date
        If Not IsNull(Me.txtEndDate) Then
            If strFilter <> "" Then
                strFilter = strFilter & _
                    " AND ConversationDate >= #" & Me.txtEndDate & "#"
            Else
                strFilter = strFilter & _
                    "ConversationDate >= #" & Me.txtEndDate & "#"
            End If
        End If
 
        'Check the Keyword
        If Not IsNull(Me.txtKeyword) Then
            If strFilter <> "" Then
                strFilter = strFilter & _
                    " AND Keyword Like '*" & Me.txtKeyword & "*'"
            End If
            fWhere = True
        End If
 
 
        With Me.frmSearchResults!Form
            .Filter = strFilter
            .FilterOn = True
        End With
        Exit Sub
EH:
    MsgBox "There was an error applying filters! " & _
        "Please contact your Database Administrator.", vbCritical, "Error!"
        Exit Sub
End Sub

This code is under a search form that has a secondary form tied to it in datasheet view, containing all of the data entries. The hope of this code was that the user would input the criteria into each section, and it would automatically update the table below. Whenever I run this code, however, the following error comes up:

Microsoft Office Access can't find the object 'ApplyFilters.'
If 'ApplyFilters' is a new macro or macro group, make sure you have saved it and that you have typed its name correctly.

In both databases, I have quintouple checked that the syntax matches up and the headings all reference each other correctly.

I appologize for how lengthy that was, but would be entirely greatful for anyone who could help me with this issue. I don't need a solution for both SQL and VBA. Whichever one of the two solves the problem, I will use that one. I just posted both to give options for troubleshooting in either way.

Once again, I appreciate the help anyone is willing to provide. Cheers.

 

[DK88]

The issue got solved on one of the other forums. Here's the SQL code for anyone who may end up having a similar issue:

1. SELECT
2. Conversations.Project,
3. Conversations.Contract,
4. Conversations.ConversationDate,
5. Conversations.Keyword,
6. Conversations.Conversation
7. FROM Conversations
8. WHERE
9. (Project=Forms![Conversation Search Form]!txtProject Or Forms![Conversation Search Form]!txtProject is null) And
10. (Contract=Forms![Conversation Search Form]!txtContract Or Forms![Conversation Search Form]!txtContract is null) And
11. (ConversationDate=Forms![Conversation Search Form]!txtStartDate Or Forms![Conversation Search Form]!txtStartDate is null) And
12. (Keyword=Forms![Conversation Search Form]!txtKeyword Or Forms![Conversation Search Form]!txtKeyword is null);