Eliminating Blank Results from Query

[DK88]

I have spent numerous hours now searching for a solution to this problem, but everything I have tried just hasn't work. I will try to be as detailed as possible to help explain the problem.

I have a table (we'll name TAB1) with five columns: Project #, Contract #, Date, Keyword, Conversation. Project # and Contract # are number formats, Date is a date format, Keyword is text format, and Conversation is memo format.

I have a query (we'll call QUER1)that searches through all of those categories except for the Conversation (as it is memo and is too complicated to search).

I have a form (we'll call FORM1) tied to this query that allows the user to input what they want to search.
I was able to code the query so that the user could enter any or all parts of the form to do the search, and whatever blank sections wouldn't be taken into account.
So if I input search data into Project # and Contract # and leave the rest blank, Date and Keyword are both Null, and the form returns any values that matched my input for Project # and Contract #, regardless of what Date and Keyword were on the entries.

This all works perfectly and I have no problems there. When the table is full of data and every cell has data in it, the search works fine.

Here's where the problem is:
When a user inputs a new entry and leaves some of the categories blank (i.e. They enter a Project # of 1 and a Contract # of 2 but leave the rest blank), the query then doesn't search properly. When you conduct a search and only enter to search for a Project Number of 3 or something, the other categories should be Null and it should only return Project Numbers that are 3. But since the Date and Keyword categories were left blank, it yields the entry as a positive result every time, even though the Project # doesn't match the search.

I've tried using the NZ function to change the Null or blank entries (although this isn't the ideal solution, because I don't want to change blank entries to 0, I'd rather just leave them blank). I've tried messing around with "Is Not Null" searching and just about everything, and I can't figure it out.

I hope I explained that well enough, because it is a slightly confusing problem. Please ask any clarifying questions if you don't understand. Thanks!

[DK88]

Rabbit: I fixed the SQL as you have said and got the form to at least run the query. When I run them, however, I don't get any results in the table at all.

This feels like I am going backwards about ten steps, because I spent a day or two of troubleshooting to get SQL code that works exactly as I need it to (mentioned in post 3 and 5), except for the original problem of this entire thread.

I need the user to be able to search critera and see the results that match that critera, but the problem is that when there are blank cells within a row, every search under my old SQL yields that row as positive, which I don't want.

If this can be done in SQL, if you could show me the exact syntax to get this to work, that would be great. I tried messing around with the Nz function, but never was able to get it to work as I needed. And I think I still need the "Is Null" in the SQL because the other search sections need to be ignored if the user does not input any criteria into them.

I recognize that SQL may run slower than VBA, but maybe that would work for this database as it only contains one table that will never have more than maybe a 1000 entries over its lifetime (And 1000 would be the extreme high end, it is likely to only contain 100 or so at most).

If it would be better in VBA, could you show me where/what VBA to use? I don't know much about VBA, but it seems like the code twinnyfo is giving me is extremely close to being exactly what I need, but I keep getting errors and nothing is working.

Thanks again for your time. If the response doesn't come within the next hour, I won't be able to try anything until Monday, because I don't have access to Access on my personal computer (har-d-har-har).

[twinnyfo]

DK,

Your main form VBA module should look something like this:

Private Sub Me.txtProject_AfterUpdate()
    ApplyFilters
End Sub
Private Sub Me.txtStartDate_AfterUpdate()
    ApplyFilters
End Sub
Private Sub Me.txtEndDate_AfterUpdate()
    ApplyFilters
End Sub
Private Sub Me.txtKeyword_AfterUpdate()
    ApplyFilters
End Sub
 
Private Sub ApplyFilters() 
 
   'This is the same code we have been playing with
   'All should be in the same module on the same form
 
End Sub

Is this what you have? The error you have been getting, that Access can't find Apply Filters, sounds like you have the ApplyFilters procedure in a different module.

I have, essentially, this same exact code through my db when I am looking at multiple records that need to be filtered, and it has never failed. So, I am at a loss at to why it won't work with your DB.

Rabbit? NeoPa? ZMBD? Maybe one o' all y'all has some insight that we all are just missing here.....

[DK88]

I don't have lines 1-12 on the code you just posted...I was never told to add that in. When I go to add it in right now, it highlights red and says:
Compile error:
Expected: identifer
highlighting the "Me" before the dot.

[DK88]

I just put it in without the Me., but it still has the same error as earlier. And if I put the Me. in, it has the above mentioned error. Here's the overall code currently.

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

[DK88]

For what it's worth, I think I like this Subform datasheet format. If we can get the search to work properly, users can change their criteria and see the data right there on the form, and then press a button that would generate a report when they have the data they were looking for. So this will work much better than the previous method of two separate buttons for a separate form and report, presuming we can get the darned code to work! haha

[twinnyfo]

Oooops -- sorry about the "Me." that I left in when I copied and pasted. It should not have the me.

So, if you code on your main form is exactly like you have it in post #55, you are still getting the "Cannot find ApplyFilters" error?

And all your controls are named exactly as the controls listed in the Code?

[DK88]

Sorry I wasn't able to reply until now. Yes, my code is exactly as it is posted in Post #54 (technically, there are about 80 lines of extra code beneath that, but I put a ' on every line and tripled checked that it is greened out and not relevant). I checked the controls and syntax and it all matches up, but whenever I use the form, I get the error from before:

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.

[twinnyfo]

Let's get weird. Lines 14, 80 and 84 from Post #54, change "Sub" to "Function". And Line 14, change "Private" to "Public". This should absolutely not make any difference at all, because everything is on the same form and VBA calls Sub just as easily as Functions. This is mind-numbing....

[DK88]

At this point, I suppose anything is worth a shot. Unfortunately, the same error. :/ I really do appreciate how much you have put into trying to figure this thing out. I'm kind of at my wits end about it too. I have spend about a full week trying to troubleshoot this, to no avail. It's so frustrating, because I spent about a week to set up and troubleshoot the rest of the entire database, and now this one silly issue cannot be fixed!

I've searched so many questions about similar issues and tried numerous options (almost exclusively through SQL, considering I don't really know how to code in VBA). The closest I got was to add an "And Is Not Null" to each category's column, i.e:

[Forms]![Conversation Search Form]![Project #] And Is Not Null

or

[Forms]![Conversation Search Form]![Project #] & (Is Not Null)

This is in the backup database before the changes to syntax, although I don't think that's causing a problem. The first code doesn't make any changes to the original problem, and the second one comes up with a run-time error (which I think is the issue with SQL running large searches, as a couple people have said now).

I just don't know what else to try...YEARG! :P

[twinnyfo]

I am at a loss, too. I have code built the same exact way and it works perfectly. I have run out of alternative ideas. I wish I could offer more.....

[Rabbit]

We don't normally do this but since we have stalled, please attach a zipped copy of your database to the thread. In 2007 format or earlier.

[DK88]

Okay. How do I attach it to the thread?

[Rabbit]

Below the reply box, click advanced. Scroll down a little and there is a manage attachments button.

[DK88]

Okay, here's the database. Hopefully looking at it will make the troubleshooting significantly easier, and hopefully the problem isn't something as silly as a missing comma or mis-spelled word.

[Rabbit]

A few things.

1) I don't see a query where you used my SQL.

2) Here is a modified SQL that will work.

SELECT 
     Conversations.Project, 
     Conversations.Contract, 
     Conversations.ConversationDate, 
     Conversations.Keyword, 
     Conversations.Conversation
FROM Conversations
WHERE 
     (Project=Forms![Conversation Search Form]!txtProject Or Forms![Conversation Search Form]!txtProject is null) And 
     (Contract=Forms![Conversation Search Form]!txtContract Or Forms![Conversation Search Form]!txtContract is null) And 
     (ConversationDate=Forms![Conversation Search Form]!txtStartDate Or Forms![Conversation Search Form]!txtStartDate is null) And 
     (Keyword=Forms![Conversation Search Form]!txtKeyword Or Forms![Conversation Search Form]!txtKeyword is null);

3) The events tab of your controls should reference an event procedure. Put anything else in there Access will try to find a macro with that name. And you're not using macros.

4) Line 76 of post 54, the last piece should be .Form and not !Form

[DK88]

By Jove I think you've got it!! There's light at the end of the tunnel! The frustrating VBA code still doesn't work (even with the fixed typo), but I put the SQL into one of the backups I had where the button code simply ran the query, and it fixed the problem I had! The only thing I would ask is how to fix the dates with this new SQL? I want the user to be able to put in a start date only (to search any date after the start date), an end date only (to search any date before the end date), or both (to search a range of dates between the start and end date). The SQL code you have only takes the Start date into account.

Despite that minor issue though, I think this is exactly what I need! Thank you so much!

I'm not sure what you mean about the events section in your 3rd point though. What object did I have the wrong events procedures written on, and what should they be?

And the SQL you gave me wasn't on the database I posted because I have been testing through multiple backups with this database, so I only posted the one where we were trying to solve the problem with VBA. The SQL database I was using is entirely different.

[DK88]

And twinnyfo, thank you very much as well! You stuck with this despite how frustrating and lasting it got without any seeming success, and I really do appreciate that.

[DK88]

Sorry for the repetative posts, but also the keyword section. Previously, I had used this for the date:

((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 this for the keyword:

((Conversations.[Keyword:]) Like "*" & [Forms]![Conversation Search Form]![Keyword] & "*")

How would I incorporate these into the new SQL?

[Rabbit]

You can replace the date section with what you have.

You can replace the first half of the keyword section with what you have.

Also, you'll need to fix your field and control references. That's not what they're named in the database and form.

[DK88]

I'm getting an error "Extra in query expression". I hate to be so helpless, but could you post the exact SQL incorporating the date and keyword changes? It should allow the user to enter either date for their search, without needing both. With the SQL you posted, the txtEndDate isn't in the SQL at all, so entering an End Date does nothing. The code I just tried was:

SELECT Conversations.Project, Conversations.Contract, Conversations.ConversationDate, Conversations.Keyword, Conversations.Conversation
FROM Conversations
WHERE (Project=Forms![Conversation Search Form]!txtProject Or Forms![Conversation Search Form]!txtProject Is Null) And  (Contract=Forms![Conversation Search Form]!txtContract Or Forms![Conversation Search Form]!txtContract Is Null) And
((Conversations.[ConversationDate]) Between Nz(Forms![Conversation Search Form]!txtStartDate,#1/1/1900#) And Nz(Forms![Conversation Search Form]!txtEndDate,#9/9/2999#)) And
((Conversations.[Keyword]) Like "*" & Forms![Conversation Search Form]!Keyword & "*") Or Forms![Conversation Search Form]!txtKeyword Is Null);

which got the error and won't work.

If you could post the new SQL, I'd appreciate it very much. Cheers.

[Rabbit]

You have an extra closing parentheses )

The last line should be

((Conversations.[Keyword]) Like "*" & Forms![Conversation Search Form]!Keyword & "*" Or Forms![Conversation Search Form]!txtKeyword Is Null); 

[DK88]

Okay, we're getting sooo close. I took out the parentheses and the date sections work perfectly, but the keyword section doesn't filter anything. Searching anything yields all of the entries as positive results. I need it to search for any string of characters in the keyword column.

I.E. searching "oo" would yield "Good" and "food" and "Shoo" and "Oodles", but not any section that doesn't have "oo" consecutively.

[DK88]

I hate to say it, but I think there's still a problem with the SQL (besides the keyword syntax). I just realized that when you enter no criteria into the search form and then open up the results (by clicking either the table or report button), it doesn't show you the entries that have missing data. If no search criteria is entered at all, all of the entries should be considered positive results. Hopefully this is an easy fix? Is there some quick VBA code we could tie into the button that says that if no criteria is put into the form, all entries in the table are returned?

EDIT: Actually, the way the date is syntaxed, entering no criteria still yields results that have a date as positive (I think because the ConversationDate column in the table isn't explicitly part of the SQL, but rather split up into StartDate and EndDate), so I think it actually isn't a problem. There should always be a date entered with the purpose of this database, so I think it actually works out on its on. Phew.

[Rabbit]

That's because the control on your form is named txtKeyword, not Keyword.

If you want the missing dates, then you can check if it's null.

[DK88]

That's it! It works exactly as I need it to work! :) Thank you very much to both of you for all of the help!! I will definitely be coming back to this site if I need any other Access help. Cheers!

[twinnyfo]

Glad this one is finally resolved. Hope to see you again!