Date Query in a Database

Posted on 2005-05-13
Last Modified: 2010-04-23
I am using Access and to develop a application. What is the best way to query for a date and see if it falls within the last 10 days.I need to send that information in an email.

Question by:karthikram
    LVL 7

    Accepted Solution

    This will Query between the dates and export to excell

        Private Sub ExtractData()
            'Initializes Excel and creates a new workbook/worksheet
            Dim excelApp As New Excel.Application
            Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
            Dim excelWorksheet As Excel.Worksheet = _
                CType(excelBook.Worksheets(1), Excel.Worksheet)
            'Makes Excel invisible to the user until spreadsheet is populated
            excelApp.Visible = False
            With excelWorksheet
                'Creates connections to pull data to DataSet
                Dim ds As New DataSet("DataSetName")
                Dim dr As DataRow
                Dim eDate As Date
                eDate = Now()
                Dim dDate As Date
                dDate = DateAdd("d", -10, (Now))

                Dim myConnection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\Production DBF Files\Data.mdb;Persist Security Info=False")
                Dim myAdapter As New OleDb.OleDbDataAdapter
                Dim myCommand As New OleDb.OleDbCommand("SELECT  [Date], SalesOrder  FROM   TblOutstandingOrders  where Date BETWEEN # " & dDate & " # AND # " & eDate & " #  ")
                Dim i As Integer = 2
                'Bind myAdapter to myCommand
                    myCommand.CommandType = CommandType.Text
                    myAdapter.SelectCommand = myCommand
                    myAdapter.SelectCommand.Connection = myConnection
                Catch ex As Exception

                End Try
                'Format cell headings
                .Range("A1").Value = " Date "
                .Range("A1").Font.Bold = True
                .Range("A1").ColumnWidth = 10
                .Range("B1").Value = " Order "
                .Range("B1").Font.Bold = True
                .Range("B1").ColumnWidth = 35.71

                'Populate Excel spreadsheet

                    For Each dr In ds.Tables(0).Rows
                        .Range("A" & i.ToString).Value = dr("date")
                        .Range("B" & i.ToString).Value = dr("salesorder")

                        i += 1
                Catch ex As Exception

                End Try
                'Make Excel visible
                excelApp.Visible = True

            End With

        End Sub

        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        End Sub

    Author Comment

    IS there a way to save the excell spreadsheet without opening ? I want to sent it a a attachment.
    LVL 7

    Expert Comment


    'Make Excel visible
    excelApp.Visible = True



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Training Course: Java/J2EE and SOA

    This course will cover both core and advanced Java concepts like Database connectivity, Threads, Exception Handling, Collections, JSP, Servlets, XMLHandling, and more. You'll also learn various Java frameworks like Hibernate and Spring.

    1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
    The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now