Filter between two data

[Ivy]

Hi, I need to filter beetween two data for "DataProvvedimento": ([DataInizioRicerca] and [DataFineRicerca]), but it doesn't work:

Private Sub ApplicaFiltro_Click()
    Dim strFilter As String
    Dim searchValue As String
    Dim dataInizio As Date
    Dim dataFine As Date

   strFilter = ""

    'This is an another search box and it works, but that's another thing
    searchValue = Replace(Me![CasellaRicercaProv], "'", "''")
    If Not IsNull(Me![CasellaRicercaProv]) And Me![CasellaRicercaProv] <> "" Then
        strFilter = "[NumeroProvvedimento] Like '*" & searchValue & "*' " & _
                    "OR [Cognome] Like '*" & searchValue & "*' " & _
                    "OR [Nome] Like '*" & searchValue & "*' "
    End If

    'Filter for data
    If IsDate(Me![DataInizioRicerca]) And IsDate(Me![DataFineRicerca]) Then
        dataInizio = Me![DataInizioRicerca]
        dataFine = Me![DataFineRicerca]
        
        If strFilter <> "" Then
            strFilter = strFilter & " AND "
        End If
        strFilter = strFilter & "((DataProvvedimento >= #" & Format(dataInizio, "dd\/mm\/yyyy") & "#) AND (DataProvvedimento <= #" & Format(dataFine, "dd\/mm\/yyyy") & "#))"
    End If

    Debug.Print strFilter

    'I have the results in the subform p_CercaProvvedimentoSubM
    With Me![p_CercaProvvedimentoSubM].Form
        .Filter = strFilter
        .FilterOn = True
    End With
End Sub

I use format date "dd/mm/yyyy", forse example: DataProvvedimento is 18/06/2024.

[DataInizioRicerca] is the search start date and [DataFineRicerca] is the search end date.

The various controls (ApplicaFiltro button, [CasellaRicercaProv] for another type of search, [DataInizioRicerca] and [DataFineRicerca]) are all on the main form p_CercaProvvedimentoM, while the records are in the subform p_CercaProvvedimentoSubM ([NumeroProvvedimento], [DataProvvedimento], [Cognome], [Nome])

Thank you very much in advance

 

[sonic8]

I need to filter beetween two data for "DataProvvedimento": ([DataInizioRicerca] and [DataFineRicerca]), but it doesn't work:

What does "it doesn't work" mean exactly?

For date values in SQL you must use either the ISO date format (YYYY-MM-DD) or the US date format (MM/DD/YYYY).
For further reference I recommend my text The Date Data Type in VBA and Access.

 

[Ivy]

The filter does not filter the date:

• Record1 with DataProvvedimento 01/05/2007
• Record2 with DataProvvedimento 15/05/2007
• Record3 with DataProvvedimento 15/04/2006

I search (in DataInizioRicerca) from 14/05/2007 to 18/06/2024 (in DataFineRicerca) but I have always all three records, instead I should see only Record2

 

[sonic8]

Did you notice my hint regarding the date format? - I originally mistyped the US format. If you just read my post in the notification email it might still have been incorrect there.
The date format definitely is a problem you must fix!

Beyond that, you should enclose the first criteria block with the ORs in parentheses to separate those conditions from the date range filter. Otherwise each of the OR conditions on its own will be enough for a record to be included in the results.