CODEWITHSUNDEEP
sqlvb.netms-access
Rkmunusamy
Rkmunusamy

Reputation: 69

Query records between two dates using VB.net/MS Access

i am having table in ms access named as stockitems, table structure is..

stdate     stitems
01-04-2015   Red
02-04-2015   Blue
08-04-2015   Green
01-05-2015   Grey
02-05-2015   Violet
09-05-2015   Purple
04-06-2015   Sky Blue

i am using the below code to select records from that table...

Dim report As New CrystalDecisions.CrystalReports.Engine.ReportDocument
    Dim xs, xs2, cmp As String
    xs = date1.Text
    xs2 = date2.Text
    cmp = salsqkrpt.salcom.Text
    Dim cmd1 As OleDbCommand = New OleDbCommand("SELECT * from stockitems  where stdate    between '" & date1.Text & "'and '" & date2.Text & "'",con)
    myDA = New OleDbDataAdapter(cmd1)
    myDataSet = New DataSet()
    myDA.Fill(myDataSet, "stockitems ")
    report.Load(Application.StartupPath & "\stockreport.rpt")
    report.SetDataSource(myDataSet.Tables("stockitems "))
    stckreport.CrystalReportViewer1.ReportSource = report

here date1 and date2 was masked textbox ,mask is 00-00-0000 (DD-MM-YYYY) when excuting this code the report only get from first two strings only that is dd only,when i assign date1=01-04-2015 and date2=03-05-2015 the result comes like...

      stdate     stitems
    01-04-2015   Red
    02-04-2015   Blue
    01-05-2015   Grey
    02-05-2015   Violet

  but my expected result is 

    stdate     stitems
    01-04-2015   Red
    02-04-2015   Blue
    08-04-2015   Green
    01-05-2015   Grey
    02-05-2015   Violet

it does'nt works as a date please help me out of this problem developers and i am beginner to vb.net. thanks in advance.

Upvotes: 0

Views: 16042

Answers (4)

user7625651
user7625651

Reputation: 1

In MS-ACCESS OR SQL The DataType Of Date Field Should be Date/Time. And Those Format Should be "DD/MM/YYYY" Then we do not need to change in our standard code...

Upvotes: 0

Ciarán
Ciarán

Reputation: 3057

MS/Access interprets dates as mm/dd/yy and so you are selecting data from January 4th -> March 5th. Reformat your dates in the SQL as mm-dd-yyyy. Generally Ms/Access actually requires the date literals in the format #mm/dd/yyyy# (i.e. with the #'s).

Dim dtDate1 as DateTime = DateTime.Parse(date1.text)
Dim dtDate2 as DateTime = DateTime.Parse(date2.text)

Dim cmd1 As OleDbCommand = New OleDbCommand("SELECT * from stockitems where stdate Between #" & _
                                            dtDate1.ToString("MM/dd/yyyy") & "# And #" & _
                                            dtDate2.ToString("MM/dd/yyyy") & "#",con)

Your code leaves you open to SQL Injection. Validate the dates first, and then either pass them as parameters or format your SQL using DataTime variables (above).

Upvotes: 4

Thomas G
Thomas G

Reputation: 10226

MS Access internal storage format for dates is US default: MM/DD/YYYY If your windows locale date format is DD/MM/YYYY like in Europe, you should always do a format() prior to querying on your dates.

As a result, your query should be :

Dim cmd1 As OleDbCommand = New OleDbCommand("SELECT * from stockitems  where stdate    between #" & format(date1.Text,"MM/DD/YYYY") & "# and #" & format(date2.Text,"MM/DD/YYYY") & "#",con)

or with - separator like you did :

Dim cmd1 As OleDbCommand = New OleDbCommand("SELECT * from stockitems  where stdate    between #" & format(date1.Text,"MM-DD-YYYY") & "# and #" & format(date2.Text,"MM-DD-YYYY") & "#",con)

Upvotes: 0

Gustav
Gustav

Reputation: 55981

You need properly formatted date expressions in your SQL:

Dim xs1 As string
Dim xs2 As string
Dim sql as string

xs1 = Date.Parse(date1.Text).ToString("yyyy'/'MM'/'dd")
xs2 = Date.Parse(date2.Text).ToString("yyyy'/'MM'/'dd")
sql = "select * from stockitems where stdate between #" & xs1 & "# and #" & xs2 & "#"
Dim cmd1 As OleDbCommand = New OleDbCommand(sql, con)

Upvotes: 2

Related Questions