none
Search between two dates RRS feed

  • Question

  • HI

    I want to search between two dates in the database
    Problem with the following code I want to arrange the date after the search ascending
    This code works well if the date is not arranged  " ORDER BY TYPY_DATE "

     Dim day As Date = DATETIM_START.Text
            Dim a, b As String
            a = DATETIM_START.Text
            a = Format(day, "MM/dd/yyyy")
    
    
            Dim sday As Date = DATETIM_END.Text
            b = DATETIM_END.Text
            b = Format(sday, "MM/dd/yyyy")
    
            'WORK CODE
            Dim DataAdapter As New OleDbDataAdapter("SELECT TYPY_ID,TYPY_CODE,TYPY_MONY,TYPY_PRICE,TYPY_DATE FROM  TYPY_TB  WHERE TYPY_DATE  >= #" & a & "#  And TYPY_DATE < #" & b & "#", con)
    
            'NOT WORK CODE
            ' Dim DataAdapter As New OleDbDataAdapter("SELECT TYPY_ID,TYPY_CODE,TYPY_MONY,TYPY_PRICE,TYPY_DATE FROM  TYPY_TB  WHERE TYPY_DATE  >= #" & a & "#  And TYPY_DATE < #" & b & "# ORDER BY TYPY_DATE", con)
    
            'NOT WORK CODE
            ' Dim DataAdapter As New OleDbDataAdapter("SELECT TYPY_ID,TYPY_CODE,TYPY_MONY,TYPY_PRICE,TYPY_DATE FROM  TYPY_TB  WHERE TYPY_DATE  >= #" & a & "#  And TYPY_DATE < #" & b & "#""ORDER BY TYPY_DATE", con)


    • Edited by ahmeddc Saturday, January 5, 2019 9:43 PM
    Saturday, January 5, 2019 9:29 PM

Answers

  • Okay let's set the stage which is to start with writing our SELECT inside of MS-Access, in this case there are three tables, Orders, Shippers and Customers (simply because I only have Orders table with dates and remaining fields will not help visualize things).

    Important:

    • I'm showing a good deal to get an idea what you expect by using samples I just wrote. 
    • Sometimes sorting a SELECT may appear to provide incorrect results but that is usually a misunderstanding of how a sort works compared to what a developer believes it should be.

    Between two dates, no sort

    SELECT O.OrderDate, S.CompanyName, O.ShippedDate, Cust.CompanyName
    FROM (Shippers AS S INNER JOIN Orders AS O ON S.ShipperID = O.ShipVia) 
    INNER JOIN Customers_Old AS Cust ON O.CustomerID = Cust.CustomerID
    WHERE (((O.OrderDate) Between #7/4/1996# And #8/12/1996#));
    

    Output looks good.

    Now add an order by

    SELECT O.OrderDate, S.CompanyName, O.ShippedDate, Cust.CompanyName
    FROM Shippers AS S INNER JOIN (Customers_Old AS Cust 
    INNER JOIN Orders AS O ON Cust.CustomerID = O.CustomerID) ON S.ShipperID = O.ShipVia
    WHERE (((O.OrderDate) Between #7/4/1996# And #8/12/1996#))
    ORDER BY O.OrderDate;
    

    Same output as the first query.

    Moving to code

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim ops As New DatabaseOperations
            DataGridView1.DataSource = ops.DemoForForum(#7/4/1996#, #8/12/1996#)
        End Sub
    End Class

    Backend

    Public Function DemoForForum(startDate As DateTime, endDate As DateTime) As DataTable
        Using cn As New OleDbConnection(_builder.ConnectionString)
            Using cmd As New OleDbCommand With {.Connection = cn}
                cmd.CommandText =
                    <SQL>
                        SELECT O.OrderDate, S.CompanyName As Shipper, O.ShippedDate, Cust.CompanyName As Company
                        FROM Shippers AS S INNER JOIN (Customers_Old AS Cust 
                        INNER JOIN Orders AS O ON Cust.CustomerID = O.CustomerID) ON S.ShipperID = O.ShipVia
                        WHERE (((O.OrderDate) Between @StartDate And @EndDate))
                        ORDER BY O.OrderDate;
                    </SQL>.Value
    
                Dim dt As New DataTable With {.TableName = "Customer"}
                cmd.Parameters.AddWithValue("@StartDate", startDate)
                cmd.Parameters.AddWithValue("@EndDate", endDate)
                Try
    
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                Catch ex As Exception
                    'mHasException = True
                    'mLastException = ex
                End Try
    
                Return dt
    
            End Using
        End Using
    End Function


    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 ahmeddc Saturday, January 5, 2019 10:43 PM
    Saturday, January 5, 2019 10:19 PM
    Moderator

All replies

  • Hello,

    Have you resolve the last question you ask and have not marked as answered and if not answered never responded to me asking so. Once you have we can talk about this question or wait for someone else to reply.


    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

    Saturday, January 5, 2019 9:49 PM
    Moderator
  • Hello,

    Have you resolve the last question you ask and have not marked as answered and if not answered never responded to me asking so. Once you have we can talk about this question or wait for someone else to reply.


    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

    I am very sorry that you have finished the previous question and the best answer 

    Saturday, January 5, 2019 9:54 PM
  • Okay let's set the stage which is to start with writing our SELECT inside of MS-Access, in this case there are three tables, Orders, Shippers and Customers (simply because I only have Orders table with dates and remaining fields will not help visualize things).

    Important:

    • I'm showing a good deal to get an idea what you expect by using samples I just wrote. 
    • Sometimes sorting a SELECT may appear to provide incorrect results but that is usually a misunderstanding of how a sort works compared to what a developer believes it should be.

    Between two dates, no sort

    SELECT O.OrderDate, S.CompanyName, O.ShippedDate, Cust.CompanyName
    FROM (Shippers AS S INNER JOIN Orders AS O ON S.ShipperID = O.ShipVia) 
    INNER JOIN Customers_Old AS Cust ON O.CustomerID = Cust.CustomerID
    WHERE (((O.OrderDate) Between #7/4/1996# And #8/12/1996#));
    

    Output looks good.

    Now add an order by

    SELECT O.OrderDate, S.CompanyName, O.ShippedDate, Cust.CompanyName
    FROM Shippers AS S INNER JOIN (Customers_Old AS Cust 
    INNER JOIN Orders AS O ON Cust.CustomerID = O.CustomerID) ON S.ShipperID = O.ShipVia
    WHERE (((O.OrderDate) Between #7/4/1996# And #8/12/1996#))
    ORDER BY O.OrderDate;
    

    Same output as the first query.

    Moving to code

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim ops As New DatabaseOperations
            DataGridView1.DataSource = ops.DemoForForum(#7/4/1996#, #8/12/1996#)
        End Sub
    End Class

    Backend

    Public Function DemoForForum(startDate As DateTime, endDate As DateTime) As DataTable
        Using cn As New OleDbConnection(_builder.ConnectionString)
            Using cmd As New OleDbCommand With {.Connection = cn}
                cmd.CommandText =
                    <SQL>
                        SELECT O.OrderDate, S.CompanyName As Shipper, O.ShippedDate, Cust.CompanyName As Company
                        FROM Shippers AS S INNER JOIN (Customers_Old AS Cust 
                        INNER JOIN Orders AS O ON Cust.CustomerID = O.CustomerID) ON S.ShipperID = O.ShipVia
                        WHERE (((O.OrderDate) Between @StartDate And @EndDate))
                        ORDER BY O.OrderDate;
                    </SQL>.Value
    
                Dim dt As New DataTable With {.TableName = "Customer"}
                cmd.Parameters.AddWithValue("@StartDate", startDate)
                cmd.Parameters.AddWithValue("@EndDate", endDate)
                Try
    
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                Catch ex As Exception
                    'mHasException = True
                    'mLastException = ex
                End Try
    
                Return dt
    
            End Using
        End Using
    End Function


    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 ahmeddc Saturday, January 5, 2019 10:43 PM
    Saturday, January 5, 2019 10:19 PM
    Moderator