[RESOLVED] Filter Rows by Date

[AndreB82]

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:

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.

[si_the_geek]

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:

        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:

        Dim date2 As String = TextBox1.Text.Trim & "-12-31 23:59:59"

[AndreB82]

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.

[si_the_geek]

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).

[AndreB82]

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.