Multi-Search Form including StartDate and EndDate - dates not working with keyword

[BarbaraChurchill]

Hi,


I am trying to create a search form that creates a list of desired results. My date fields work when I only use both of them. The text fields work (including the combo box) when I use them - but when I use the text (keyword search) AND the "between StartDate AND EndDate", it's like that portion of the search string is ignored. And when I only type in a StartDate, no results are returned. I know I am missing some code - can you tell me that might be?

This is the code I am using (I found 2 online & tweaked - I can provide the source if needed):

Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.Keyword_Search) Then
        strWhere = strWhere & "([Description] Like ""*" & Me.Keyword_Search & "*"") OR "
    End If
    
   If Not IsNull(Me.Keyword_Search) Then
        strWhere = strWhere & "([Title] Like ""*" & Me.Keyword_Search & "*"") OR "
    End If
    
    If Not IsNull(Me.Keyword_Search) Then
        strWhere = strWhere & "([Subject] Like ""*" & Me.Keyword_Search & "*"") AND "
    End If

If Not IsNull(Me.CmbFormat) Then
        strWhere = strWhere & "([PhysicalFormat] = """ & Me.CmbFormat & """) AND "
    End If

If Not IsNull(Me.StartDate) Then
          strWhere = strWhere & "([OriginalDate] between #" & (Me.StartDate) & "# AND #" & (Me.EndDate) & "#) AND "
       Else
       strWhere = strWhere & "([OriginalDate] >= #" & Me.StartDate & " #"
       strWhere = strWhere & "([OriginalDate] <= #" & Me.EndDate & " #"
    End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then

Else
strWhere = Left$(strWhere, lngLen)

Debug.Print strWhere

Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub

Private Sub cmdReset_Click()
        
     'Clear all the controls in the Form Header section.
    For Each ctl In Me.Section(acHeader).Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox
            ctl.Value = Null
        Case acCheckBox
            ctl.Value = False
        End Select
    Next
    
    'Remove the form's filter.
    Me.Filter = "(False)"
 
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
   Cancel = True
End Sub

Private Sub Form_Open(Cancel As Integer)
    Me.Filter = "(False)"
    Me.FilterOn = True
End Sub

Thanks for any help!
Barbara

[June7]

Try:

If Not IsNull(Me.StartDate) AND Not IsNull(Me.EndDate) Then
...
ElseIf Not IsNull(Me.StartDate) Then
...
ElseIf Not IsNull(Me.EndDate) Then
...
End If

Don't forget the " AND " at the end of each of the date criteria. You left off of the alternate date statements. The Left truncation will fail.

[BarbaraChurchill]

Try:

If Not IsNull(Me.StartDate) AND Not IsNull(Me.EndDate) Then
...
ElseIf Not IsNull(Me.StartDate) Then
...
ElseIf Not IsNull(Me.EndDate) Then
...
End If

Don't forget the " AND " at the end of each of the date criteria. You left off of the alternate date statements. The Left truncation will fail.

________________

Thank you!!!

However, I have to admit that I am not really versed in code...Not sure what the alternate date statements should be, nor which "AND" I need to add. Also - do your 3 dots indicate other text that should be included?

Thank you again for your time & quick response.
Barbara

[June7]

The 3 dots mean enter the necessary code.

You are missing " AND " at the ends of:
strWhere = strWhere & "([OriginalDate] >= #" & Me.StartDate & " #"
strWhere = strWhere & "([OriginalDate] <= #" & Me.EndDate & " #"

The alternative date statements would be each of the above lines.

[BarbaraChurchill]

Ok,
This is what that part now looks like:

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then


strWhere = strWhere & "([OriginalDate] between #" & (Me.StartDate) & "# AND #" & (Me.EndDate) & "#) AND "

ElseIf Not IsNull(Me.StartDate) Then
ElseIf Not IsNull(Me.EndDate) Then

Else
strWhere = strWhere & "([OriginalDate] >= #" & Me.StartDate & " #" And ""
strWhere = strWhere & "([OriginalDate] <= #" & Me.EndDate & " #" And ""

End If


Is that what it should look like? Am I missing some other code or did I put your suggestions in the wrong place? Because it still works the same way it did before.

Thanks for trying to help!
Barbara

[June7]

No, put the StartDate line under the StartDate ElseIf and the EndDate line under the EndDate ElseIf.

Remove the 'Else'.

Too many quote marks

ElseIf Not IsNull(Me.StartDate) Then
strWhere = strWhere & "([OriginalDate] >= #" & Me.StartDate & "# AND "
ElseIf Not IsNull(Me.EndDate) Then
strWhere = strWhere & "([OriginalDate] <= #" & Me.EndDate & "# AND "

[BarbaraChurchill]

Ok. Thank you for your reply!!

Sorry to say, it still does not pull dates "between" when including keywords in the search string. It seems to be ignoring that part of the string request. Like when a parameter query has too many parameters.

This is what I changed based on what I understood you to say:

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
strWhere = strWhere & "([OriginalDate] between #" & (Me.StartDate) & "# AND #" & (Me.EndDate) & "#) AND "

ElseIf Not IsNull(Me.StartDate) Then
strWhere = strWhere & "([OriginalDate] >= #" & Me.StartDate & " # AND "

ElseIf Not IsNull(Me.EndDate) Then
strWhere = strWhere & "([OriginalDate] <= #" & Me.EndDate & " # AND "


End If

[June7]

A parameterized query can have too many parameters?

Seems to me that should work. I do notice error in the alternate date criteria - there is a solo paren in each line, remove it or add its mate.

At this point, I would have to review db to debug. If you want to provide, follow instructions at bottom of my post.

Also, refer to link at bottom of my post for guidance on debugging techniques.

[BarbaraChurchill]

Thank you very much for helping me get the code cleaned up! Unfortunately, I still do not get the results I am looking for. This is what the query code looks like once it's run:

([Description] Like "*blue*") OR ([Title] Like "*blue*") OR ([Subject] Like "*blue*") AND ([PhysicalFormat] = "10") AND ([OriginalDate] between #3/1/2000# AND #4/1/2000#) but the dates returned are all dates in the db, not the just dates between 3/1/00 and 4/1/00 with the keyword "blue" (which should be one record).

I will try to send db.

Thank you again!
Barbara Archives - Copy.zip

[June7]

I did not notice the OR criteria before. This really complicates. Combining OR and AND operators is tricky.

Try placing the 3 OR criteria enclosed together in parentheses.

([Description] Like "*blue*" OR [Title] Like "*blue*" OR [Subject] Like "*blue*") AND

Also, the Left trim will fail if strWhere ends in " OR ".

[BarbaraChurchill]

That doesn't work either. I have been working with the "On Open" property of the SEARCH form.

[June7]

You are setting the Filter and FilterOn properties in Open event. The filter code is in button Click event. This all looks good. Have you step debugged? Because of FindingGuide table I can't run the code until I can use Access 2010 tonight.

[June7]

Finally hit me the OR criteria are all from the same control. Don't need separate If Thens. Try:

    If Not IsNull(Me.Keyword_Search) Then
        strWhere = strWhere & "([Description] Like '*" & Me.Keyword_Search & "*' OR " & _
        "[Title] Like '*" & Me.Keyword_Search & "*' OR " & _
        "[Subject] Like '*" & Me.Keyword_Search & "*') AND "
    End If
    If Not IsNull(Me.CmbFormat) Then
        strWhere = strWhere & "([PhysicalFormat] = '" & Me.CmbFormat & "') AND "
    End If
    If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
        strWhere = strWhere & "([OriginalDate] between #" & (Me.StartDate) & "# AND #" & (Me.EndDate) & "#) AND "
    ElseIf Not IsNull(Me.StartDate) Then
        strWhere = strWhere & "([OriginalDate] >= #" & Me.StartDate & " #) AND "
    ElseIf Not IsNull(Me.EndDate) Then
         strWhere = strWhere & "([OriginalDate] <= #" & Me.EndDate & " #) AND "
    End If
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
       'MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        Me.Filter = strWhere
        Me.FilterOn = True
    End If

[BarbaraChurchill]

Thank you so much for all of your time!! Unfortunately, that doesn't work either. What I noticed is that the Physical format by itself doesn't filter correctly either and with your suggested format, doesn't work at all. So - I'm really stumped!

[June7]

I tested Keyword and date range and that worked. I will test some more tonight.