Great Combo Filter/Search form, cant get DATE filter to integrate. HELP

**fau5tu5** · 05-14-2009, 06:15 PM

Ok.. SO I've been trying to build a Combo Search/Filter form for a simple 1 table db in Access 2007 (eventually will be 03 compatable but still)

I found a fairly streamlined Filter/Search Form with Report Generator that allows for at least 3 variables to be searched... (Exactly what I'm needing,..) and I believe it wont be very hard to expand on it if need be.

The issue is that it's design only addresses Text search/filter (LIKE *) and Numeric/Mathematical (>=< ) and not DATE.

Search/filter by DATE is one of the PRIMARY aspects of needed functionality.
IE: filter all cases that include (LIKE *) from field "A",
and (LIKE *) from field "B"
that are (AFTER) "2/3/2009"
(( and eventually adding a 4th search to accommodate ;
and (Before) "5/6/2009" ))

So far it looks as though the framework of this Filter/Search Form is all there..
I'm just not able to get the syntax correct for the Date function... (or likely I need to add something to have DATEs treated differently in the search design,..

Here is the code,.. but likely better if you just Dload and look at the whole thing as I'm probably missing something elsewhere..

Many have been modified, and " ' non code markers & explanations "

Lines 13-15 I added, and they function as desired ( it seems)
Lines 121-125 are where I think it may simply be a syntax issue, unless I do in fact lack the proper support code, which I would need help with.

To see or test functionality in the sample, use only "pt_last" as your text search, and "initial_receipt_date" as your date search.

If you wanted to use or try the Math/Numeric, you would need to change the "Customer" DB referrers to "8th" as my DB is named "8th" the template form used "Customers" as their DB.

Code:

Sub LoadOperations(pSearchField As Object, pSearchOperation As Object)

    Select Case pSearchField
        
        'Alphabetical search
        Case "rptr_last", "pt_last", "narrative", "any_serious"
            pSearchOperation.RowSource = "contains"
        
       'Mathematical search (this is currently not relevant as there are no simple mathematical listings ..
        Case "totals", "Mathematics" 'these fields do not exist, merely placeholders
            pSearchOperation.RowSource = "'is greater than';'is less than';'is equal to'"
        
        'Date search   THIS IS THE ADDED FUNCTIONALITY I AM TRYING TO ADD (will be "'Before';'After';'On'" eventtually )
        Case "initial_receipt_date"
            pSearchOperation.RowSource = "after"
            
    End Select
    
End Sub

Private Sub cmdClose_Click()

    'Close form
    DoCmd.Close
    
End Sub

Private Sub cboSearchField1_Change()
    
    'Load search operations
    LoadOperations cboSearchField1, cboSearchOperation1
    
End Sub

Private Sub cboSearchField1_Click()

    'Load search operations
    LoadOperations cboSearchField1, cboSearchOperation1
    
End Sub

Private Sub cboSearchField2_Change()

    'Load search operations
    LoadOperations cboSearchField2, cboSearchOperation2
    
End Sub

Private Sub cboSearchField2_Click()

    'Load search operations
    LoadOperations cboSearchField2, cboSearchOperation2
    
End Sub

Private Sub cboSearchField3_Change()

    'Load search operations
    LoadOperations cboSearchField3, cboSearchOperation3
    
End Sub

Private Sub cboSearchField3_Click()

    'Load search operations
    LoadOperations cboSearchField3, cboSearchOperation3
    
End Sub

Private Sub cmdSearch_Click()

    Dim LCaption As String

    'First search condition is mandatory
    If Len(cboSearchField1) = 0 Or IsNull(cboSearchField1) = True Then
        MsgBox "First search condition:  You must select a field to search."
        
    ElseIf Len(cboSearchOperation1) = 0 Or IsNull(cboSearchOperation1) = True Then
        MsgBox "First search condition:  You must select a search operation."
        
    ElseIf Len(txtSearchValue1) = 0 Or IsNull(txtSearchValue1) = True Then
        MsgBox "First search condition:  You must enter a search value."
        
        
    'Second search condition must be completed if started
    ElseIf Len(cboSearchField2) > 0 And (Len(cboSearchOperation2) = 0 Or IsNull(cboSearchOperation2) = True) Then
        MsgBox "Second search condition:  You must select a search operation."
        
    'Second search condition must be completed if started
    ElseIf Len(cboSearchField2) > 0 And (Len(txtSearchValue2) = 0 Or IsNull(txtSearchValue2) = True) Then
        MsgBox "Second search condition:  You must enter a search value."
        
    'Third search condition must be completed if started
    ElseIf Len(cboSearchField3) > 0 And (Len(cboSearchOperation3) = 0 Or IsNull(cboSearchOperation3) = True) Then
        MsgBox "Third search condition:  You must select a search operation."
        
    'Third search condition must be completed if started
    ElseIf Len(cboSearchField3) > 0 And (Len(txtSearchValue3) = 0 Or IsNull(txtSearchValue3) = True) Then
        MsgBox "Third search condition:  You must enter a search value."
        
    Else
    
        'Generate search criteria for first condition
        Select Case cboSearchOperation1.Value
            Case "contains"
                GCriteria = cboSearchField1.Value & " LIKE '*" & txtSearchValue1 & "*'"
                LCaption = "8th (" & cboSearchField1.Value & " contains '*" & txtSearchValue1 & "*'"
                
            Case "is greater than"
                GCriteria = cboSearchField1.Value & " > " & txtSearchValue1
                LCaption = "Customers (" & cboSearchField1.Value & " > " & txtSearchValue1 & ""
            
            Case "is less than"
                GCriteria = cboSearchField1.Value & " < " & txtSearchValue1
                LCaption = "Customers (" & cboSearchField1.Value & " < " & txtSearchValue1 & ""
            
            Case "is equal to"
                GCriteria = cboSearchField1.Value & " = " & txtSearchValue1
                LCaption = "Customers (" & cboSearchField1.Value & " = " & txtSearchValue1 & ""
            
            Case "after" 'THIS IS THE FIELD I AM TRYING TO ADD AND HAVING ISSUES WITH.
                GCriteria = cboSearchField1.Value & " > '#" & cboSearchField1.Value & "#'"
                LCaption = "8th (" & cboSearchField1.Value & " > '#" & cboSearchField1.Value & "#'"
                'Ideally I would also want to have the selection of "after" force the "txtSearchValue" to switch to a DATE/Calendar entry field with te popup calendar..
        End Select
        
        'Generate search criteria for second condition
        If Len(cboSearchField2) > 0 And Len(cboSearchOperation2) > 0 And Len(txtSearchValue2) > 0 Then
            Select Case cboSearchOperation2.Value
                Case "contains"
                    GCriteria = GCriteria & " and " & cboSearchField2.Value & " LIKE '*" & txtSearchValue2 & "*'"
                    LCaption = LCaption & " and " & cboSearchField2.Value & " contains '*" & txtSearchValue2 & "*'"
                    
            Case "is greater than"
                GCriteria = cboSearchField1.Value & " > " & txtSearchValue1
                LCaption = "Customers (" & cboSearchField1.Value & " > " & txtSearchValue1 & ""
            
            Case "is less than"
                GCriteria = cboSearchField1.Value & " < " & txtSearchValue1
                LCaption = "Customers (" & cboSearchField1.Value & " < " & txtSearchValue1 & ""
            
            Case "is equal to"
                GCriteria = cboSearchField1.Value & " = " & txtSearchValue1
                LCaption = "Customers (" & cboSearchField1.Value & " = " & txtSearchValue1 & ""
            End Select
        End If
        
        'Generate search criteria for third condition
        If Len(cboSearchField3) > 0 And Len(cboSearchOperation3) > 0 And Len(txtSearchValue3) > 0 Then
            Select Case cboSearchOperation3.Value
                Case "contains"
                    GCriteria = GCriteria & " and " & cboSearchField3.Value & " LIKE '*" & txtSearchValue3 & "*'"
                    LCaption = LCaption & " and " & cboSearchField3.Value & " contains '*" & txtSearchValue3 & "*'"
                    
           Case "is greater than"
                GCriteria = cboSearchField1.Value & " > " & txtSearchValue1
                LCaption = "Customers (" & cboSearchField1.Value & " > " & txtSearchValue1 & ""
            
            Case "is less than"
                GCriteria = cboSearchField1.Value & " < " & txtSearchValue1
                LCaption = "Customers (" & cboSearchField1.Value & " < " & txtSearchValue1 & ""
            
            Case "is equal to"
                GCriteria = cboSearchField1.Value & " = " & txtSearchValue1
                LCaption = "Customers (" & cboSearchField1.Value & " = " & txtSearchValue1 & ""
            End Select
        End If
        
        LCaption = LCaption & ")"
        
        'Filter frmmulti based on search criteria
        Form_frmmultiSearch.RecordSource = "select * from 8th where " & GCriteria
        Form_frmmultiSearch.Caption = LCaption
        
        'Close frmmultiSearch
        DoCmd.Close acForm, "frmmultiSearch"
        
        MsgBox "Results have been filtered."
        
    End If
    
End Sub

**fau5tu5** · 05-14-2009, 06:27 PM

Also,.. if someone has an easier, cleaner or superior way to achieve the same functionality.. By all means, I'm all ears.. err.. eyes on screen,. whatever.. I'm open to it and appreciative of anything...

**bhill** · 05-15-2009, 08:54 AM

Without looking at your attachment (I'm on A2K3), I'd say the issue is the single quote marks around your date. That's telling it that it's text, rendering your date to look like #1/1/2009#. Try removing the single quotes with just the pound signs.

Code:

          Case "after" 'THIS IS THE FIELD I AM TRYING TO ADD AND HAVING ISSUES WITH.
                GCriteria = cboSearchField1.Value & " > #" & cboSearchField1.Value & "#"
                LCaption = "8th (" & cboSearchField1.Value & " > #" & cboSearchField1.Value & "#"

As far as a date picker, there are a lot of them out there including some right within access (at least '03 has one included). You can find them in the activex controls.

**fau5tu5** · 05-16-2009, 01:55 AM

really silly of me not upload a 2003 version..

I am attatching a stripped down version of the original template "search42003" as well as one prepped for my needs "TemplatNeedingHelp2003"

Please take a look and see what you think...

These should be a cleaner (less convoluted junk than the earlier one with all my mixed up attempts potentially)

The goal, again, is to be able to search by a combination of funtions and to be able to easily ADD fields that can be searched by simply adding them in the selection list instead. So you would be able to add a field of say, "end_date" to the Table, and then merely add it in the Properties for the cbosearch in design view for the form, as well as the Report, as well as the begining of the VBA code listing it in the DateSearch options and have it be fully functional merely as another option to search from the pulldown.

THANKSSSS

**bhill** · 05-18-2009, 06:05 PM

if you plug in a value and step through the code, it shows a value for GCriteria as "initial_receipt_date >#initial_receipt_date#". I think you need to adjust the field that the code is pointing to from:

GCriteria = cboSearchField1.Value & " >#" & cboSearchField1.Value & "#"
to
GCriteria = cboSearchField1.Value & " >#" & txtSearchValue1.Value & "#"
All of the other values point to the txtSearchValue boxes.