none
Help! Datagridview filter date in a range

    Question

  • I'm looking for the ability to filter date in a range
    I made a test file to show the problem which I am struggling with - FILE

    Thanks for any ideas and all help :)
    Wednesday, December 06, 2017 4:15 PM

Answers

  • See the following, I downloaded your project, tweaked it to work.

    https://1drv.ms/u/s!AtGAgKKpqdWjiQWlr1R3t6a47JvX

    Imports System.Data.OleDb
    Public Class Form1
        Dim dbconn As New OleDbConnection
        Dim adt As New OleDbDataAdapter
        Dim ds As New DataSet
    
        Dim datatable As New DataTable
        Dim cmd As New OleDbCommand
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            TextBox1.Text = "2/10/2016"
            TextBox2.Text = "10/21/2017"
    
            dbconn.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source=data_keyCopy.mdb"
            showData() 'show database values in datagridview
        End Sub
        Public Sub showData()
            Dim dbcommand As String
            dbcommand = "SELECT Nr, DateValue FROM tbl_klucze"
            adt = New OleDbDataAdapter(dbcommand, dbconn)
            datatable = New DataTable
            adt.Fill(datatable)
            DataGridView1.DataSource = datatable.DefaultView
        End Sub
        Private Sub customColumnWidth() ' set custom column width
            'datagridName.Columns(columnNumber or columnName)
            Dim column0 As DataGridViewColumn = DataGridView1.Columns(0)
            column0.Width = 40 'set columnwidth
            Dim column1 As DataGridViewColumn = DataGridView1.Columns(1)
            column1.Width = 170 'set columnwidth
        End Sub
        Private Sub DateTimePicker1_CloseUp(sender As Object, e As EventArgs) 'Handles DateTimePicker1.CloseUp
            TextBox1.Text = DateTimePicker1.Text
        End Sub
    
        Private Sub DateTimePicker2_CloseUp(sender As Object, e As EventArgs) Handles DateTimePicker2.CloseUp
            TextBox2.Text = DateTimePicker2.Text
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            TextBox2.Text = ""
            TextBox1.Text = ""
            showData()
        End Sub
        Private Sub TextBox2_TextChanged(sender As Object, e As EventArgs) 'Handles TextBox2.TextChanged
            Dim dv As New DataView(datatable, "DataW >=" & TextBox1.Text & "and DataW<=" & TextBox2.Text, "DataW", DataViewRowState.CurrentRows)
            DataGridView1.DataSource = dv
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            RowFilter()
        End Sub
        Private Sub RowFilter()
            Dim dv As DataView = CType(DataGridView1.DataSource, DataView)
    
            If String.IsNullOrWhiteSpace(TextBox1.Text) OrElse String.IsNullOrWhiteSpace(TextBox2.Text) Then
                dv.RowFilter = ""
            End If
    
            If DateTime.TryParse(TextBox1.Text, Nothing) AndAlso DateTime.TryParse(TextBox2.Text, Nothing) Then
                Dim filter = $"DateValue >= #{CDate(TextBox1.Text)}# and DateValue <= #{CDate(TextBox2.Text)}#"
                dv.RowFilter = filter
            End If
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by marek_maro 13 hours 0 minutes ago
    13 hours 31 minutes ago
    Moderator

All replies

  • Not knowing how the DataGridView was loaded (if not loaded using the DataSource property then go no farther) I suggest setting up the DataSource for the DataGridView to a DataSet (DataMember would be a table name in the DataSet) or to a DataTable then set RowFilter of the DataView of the DataTable to something like

    Where dv is our DataView e.g.

    Dim dv As DataView = CType(DataGridView1.DataSource,DataTable).DefaultView

    dv.RowFilter = $"SomeDate >= {DataTimePicker1.Value} AND SomeDate <= {DataTimePicker1.Value}"

    This can be done using a BindingSource via it's Filer property.

    To remove either filter set it to an empty string.

    Note I did not look at your file as I only trust Microsoft OneDrive (which is free) so the above is not based on your file.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Wednesday, December 06, 2017 4:41 PM
    Moderator
  • Hi marek,

    In addition to use DataView to filter data, you can also use BindingSource to filter date. Please take a look the following example:

     Private Sub Form6_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim dt As New DataTable
            dt.Columns.Add("Column1", GetType(Integer))
            dt.Columns.Add("Column2", GetType(String))
            dt.Columns.Add("Column3", GetType(Date))
            dt.Rows.Add(1, "A", "2017-01-01")
            dt.Rows.Add(5, "A", "2017-12-01")
            dt.Rows.Add(2, "B", "2016-09-08")
            dt.Rows.Add(6, "B", "2016-12-08")
            dt.Rows.Add(3, "C", "2018-01-01")
            dt.Rows.Add(7, "C", "2018-12-01")
            dt.Rows.Add(4, "D", "2019-01-09")
            dt.Rows.Add(8, "D", "2019-12-09")
            BindingSource1.DataSource = dt
            BindingSource1.Sort = "Column1 ASC"
            DataGridView1.DataSource = BindingSource1
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            BindingSource1.Filter = " Column3 >=" & String.Format("#{0:yyyy-MM-dd}#", TextBox1.Text) & "and Column3 <= " & String.Format("#{0:yyyy-MM-dd}#", TextBox2.Text)
        End Sub

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 07, 2017 3:05 AM
    Moderator
  • Ok, I will try :)
    Thursday, December 07, 2017 4:31 PM
  • Hi marek,

    So have you solved this problem now?

    I think the above reply can provide you with a solution, have you tried it?

    If so, hope you can close this thread by marking the reply as answer as this will help others looking for the same or similar issues down the road.

    Best Regards,

    Stanly


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 11, 2017 1:19 AM
  • Hi, 
    this solution is great. But I've a problem to add this solution to my project.
    14 hours 30 minutes ago
  • Can you elaborate on "add this solution to my project" ?

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    14 hours 23 minutes ago
    Moderator
  • Okay, I just looked at your database, your field DataW is type string where it should be like the field I added in DataValue. So first thing is to change DataW to a Date/Time field.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    14 hours 12 minutes ago
    Moderator
  • I think it will be quick if I show what I have made. 
    So i made a oledb connection to access file, all my attempts to filter this, end up with an error.

    Imports System.Data.OleDb
    Public Class Form1
        Dim dbconn As New OleDbConnection
        Dim adt As New OleDbDataAdapter
        Dim ds As New DataSet
    
        Dim datatable As New DataTable
        Dim cmd As New OleDbCommand
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            dbconn.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source=data_key.mdb"
            showData() 'show database values in datagridview
        End Sub
        Public Sub showData()
            Dim dbcommand As String
            dbcommand = "SELECT * FROM tbl_klucze"
            adt = New OleDbDataAdapter(dbcommand, dbconn)
            datatable = New DataTable
            adt.Fill(datatable)
            DataGridView1.DataSource = datatable
        End Sub
        Private Sub customColumnWidth() ' set custom column width
            'datagridName.Columns(columnNumber or columnName)
            Dim column0 As DataGridViewColumn = DataGridView1.Columns(0)
            column0.Width = 40 'set columnwidth
            Dim column1 As DataGridViewColumn = DataGridView1.Columns(1)
            column1.Width = 170 'set columnwidth
        End Sub
        Private Sub DateTimePicker1_CloseUp(sender As Object, e As EventArgs) Handles DateTimePicker1.CloseUp
            TextBox1.Text = DateTimePicker1.Text
        End Sub
    
        Private Sub DateTimePicker2_CloseUp(sender As Object, e As EventArgs) Handles DateTimePicker2.CloseUp
            TextBox2.Text = DateTimePicker2.Text
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            TextBox2.Text = ""
            TextBox1.Text = ""
            showData()
        End Sub
        Private Sub TextBox2_TextChanged(sender As Object, e As EventArgs) Handles TextBox2.TextChanged
            Dim dv As New DataView(datatable, "DataW>=" & TextBox1.Text & "and DataW<=" & TextBox2.Text, "DataW", DataViewRowState.CurrentRows)
            DataGridView1.DataSource = dv
        End Sub
    End Class
    

    14 hours 2 minutes ago
  • See the following, I downloaded your project, tweaked it to work.

    https://1drv.ms/u/s!AtGAgKKpqdWjiQWlr1R3t6a47JvX

    Imports System.Data.OleDb
    Public Class Form1
        Dim dbconn As New OleDbConnection
        Dim adt As New OleDbDataAdapter
        Dim ds As New DataSet
    
        Dim datatable As New DataTable
        Dim cmd As New OleDbCommand
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            TextBox1.Text = "2/10/2016"
            TextBox2.Text = "10/21/2017"
    
            dbconn.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source=data_keyCopy.mdb"
            showData() 'show database values in datagridview
        End Sub
        Public Sub showData()
            Dim dbcommand As String
            dbcommand = "SELECT Nr, DateValue FROM tbl_klucze"
            adt = New OleDbDataAdapter(dbcommand, dbconn)
            datatable = New DataTable
            adt.Fill(datatable)
            DataGridView1.DataSource = datatable.DefaultView
        End Sub
        Private Sub customColumnWidth() ' set custom column width
            'datagridName.Columns(columnNumber or columnName)
            Dim column0 As DataGridViewColumn = DataGridView1.Columns(0)
            column0.Width = 40 'set columnwidth
            Dim column1 As DataGridViewColumn = DataGridView1.Columns(1)
            column1.Width = 170 'set columnwidth
        End Sub
        Private Sub DateTimePicker1_CloseUp(sender As Object, e As EventArgs) 'Handles DateTimePicker1.CloseUp
            TextBox1.Text = DateTimePicker1.Text
        End Sub
    
        Private Sub DateTimePicker2_CloseUp(sender As Object, e As EventArgs) Handles DateTimePicker2.CloseUp
            TextBox2.Text = DateTimePicker2.Text
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            TextBox2.Text = ""
            TextBox1.Text = ""
            showData()
        End Sub
        Private Sub TextBox2_TextChanged(sender As Object, e As EventArgs) 'Handles TextBox2.TextChanged
            Dim dv As New DataView(datatable, "DataW >=" & TextBox1.Text & "and DataW<=" & TextBox2.Text, "DataW", DataViewRowState.CurrentRows)
            DataGridView1.DataSource = dv
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            RowFilter()
        End Sub
        Private Sub RowFilter()
            Dim dv As DataView = CType(DataGridView1.DataSource, DataView)
    
            If String.IsNullOrWhiteSpace(TextBox1.Text) OrElse String.IsNullOrWhiteSpace(TextBox2.Text) Then
                dv.RowFilter = ""
            End If
    
            If DateTime.TryParse(TextBox1.Text, Nothing) AndAlso DateTime.TryParse(TextBox2.Text, Nothing) Then
                Dim filter = $"DateValue >= #{CDate(TextBox1.Text)}# and DateValue <= #{CDate(TextBox2.Text)}#"
                dv.RowFilter = filter
            End If
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by marek_maro 13 hours 0 minutes ago
    13 hours 31 minutes ago
    Moderator
  • YES !! Thank you. 
    It's great now, I can finely finish all work. 

    You are genius ;)
    12 hours 57 minutes ago