dcsimg
Results 1 to 5 of 5

Thread: [RESOLVED] Filter Rows by Date

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Sep 2018
    Posts
    79

    Resolved [RESOLVED] Filter Rows by Date

    Dear all,
    I'm in trouble for a datagridview where I would like to filter rows by the column Date.

    In particular I have a textbox in which I insert the Year value; then I'm building up two Strings using 01/01/+Year and 31/12/+Year and finally I would like to filter out the rows for which the Date value is outside the defined range.

    Here below the code:

    Code:
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            On Error GoTo SearchErr
    
            Dim date1 As String = "01/01/" & TextBox1.Text.Trim
            Dim date2 As String = "31/12/" & TextBox1.Text.Trim
            Dim dt1 As DateTime = DateTime.Parse(date1)
            Dim dt2 As DateTime = DateTime.Parse(date2)
    
            If TextBox1.Text = "" And TextBox2.Text = "" And IsNothing(CStr(ComboBox1.SelectedItem)) Then
                Exit Sub
            ElseIf TextBox1.Text = "" And TextBox2.Text <> "" And IsNothing(CStr(ComboBox1.SelectedItem)) Then
                dtTableGrd.DefaultView.RowFilter = "Acc Like '%" & TextBox2.Text & "%'"
            ElseIf TextBox1.Text = "" And TextBox2.Text = "" And Not IsNothing(CStr(ComboBox1.SelectedItem)) Then
                dtTableGrd.DefaultView.RowFilter = "Leva Like '%" & ComboBox1.SelectedItem.ToString & "%'"
            ElseIf TextBox1.Text <> "" And TextBox2.Text = "" And IsNothing(CStr(ComboBox1.SelectedItem)) Then
                dtTableGrd.DefaultView.RowFilter = "Data >= #" + dt1 + "# AND Data <= #" + dt2 + "#"
    
            End If
    ErrEx:
            Exit Sub
    
    SearchErr:
            MsgBox("Errore numero:" & Err.Number & vbNewLine & "Descrizione errore:" & Err.Description, MsgBoxStyle.Critical, "Errore Ricerca!")
            Resume ErrEx
        End Sub
    The code doesn't work and, debugging, the message I get is: "String was not recognized as a valid DateTime".

    Any suggestion? I guess I'm converting badly the Strings, and I did a lot of tentative but without any success...

    Thanks,
    A.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,099

    Re: Filter Rows by Date

    You are currently appending DateTime values to the filter, but should be appending String values... as such, you should replace dt1 and dt2 with date1 and date2.

    It might work like that, but I would recommend avoiding issues with formatting (which can occur based on things outside of your program) by using this format style instead:
    Code:
            Dim date1 As String = TextBox1.Text.Trim & "-01-01"
            Dim date2 As String = TextBox1.Text.Trim & "-12-31"
    You may also find that you don't get values for the 31st of December, in which case add a time too:
    Code:
            Dim date2 As String = TextBox1.Text.Trim & "-12-31 23:59:59"

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Sep 2018
    Posts
    79

    Re: Filter Rows by Date

    Dear si_the_geek,
    your suggestion worked. I changed the code in the way you wrote and it works.

    Now, as I would like to understand and getting knowledge, could you please let me understand how it works? In particular I'm curious to get why it works now that date1 and date2 have the format yyyy-MM-dd, while the data that they have to be compared to appear in the DataGridview and in the SQL Expresse table in the format dd-MM-yyyy. Could you please help me in understanding this?

    Thanks,
    A.

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,099

    Re: Filter Rows by Date

    This may surprise you, but date values in a computer do not have a format at all. They are generally stored as a number, usually as the number of days since a certain date (with the decimal part indicating the time). It's an awkward thing to get your head around initially, because it is different to what you would naturally assume.

    What does have a format is the text/string based representation of the value, and that is usually based on the settings of the computer - which can change at any moment.

    When you put a value from a String into a DateTime then it will be interpreted using the current settings (unless you specify otherwise), and when you get a value from a DateTime to a String then it will be formatted based on the current settings (unless you specify otherwise). Various things will do that formatting for you automatically, such as the DataGridView, and several of the windows within Visual Studio such as Watch.


    In this case the RowFilter will be interpreting the values (which are part of a String), and you have no control over how it will interpret them... it might assume d/m/y, or m/d/y, or y/m/d, or something else.

    Thankfully there is the format known as ISO (yyyy-mm-dd, plus a time if wanted), which will always be interpreted correctly, and as almost everything can read it, that is generally the best format to use (unless you are showing the data to the user, in which case using their settings is best).

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Sep 2018
    Posts
    79

    Re: Filter Rows by Date

    Dear si_the_geek,
    thank you so much for your explanation....almost more useful than the solution to my post.
    I really appreciated your coaching.

    Thanks,
    A.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width