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

Question

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.

What is the type of the stdate column in the database? It needs to be "Date/Time" if you want to use dates reliably and without horrible complications. — Andrew Morton
– Andrew Morton, Commented May 11, 2015 at 10:22

Ciarán · Accepted Answer · 2015-05-11 08:44:18Z

4

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

edited May 11, 2015 at 8:44

answered May 11, 2015 at 8:19

Ciarán

3,0571 gold badge18 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gustav · Accepted Answer · 2015-05-11 08:34:47Z

2

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)

answered May 11, 2015 at 8:34

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Comments

Thomas G · Accepted Answer · 2015-05-11 08:35:39Z

0

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)

answered May 11, 2015 at 8:35

Thomas G

10.3k7 gold badges33 silver badges45 bronze badges

2 Comments

Ciarán Over a year ago

The Date literal in this won't return a date formatted as required. the DD and the YYYY must be lowercase.

Rkmunusamy Over a year ago

yes sir, i have converted it and i was used it successfully sir!

user7625651 · Accepted Answer · 2017-02-26 15:05:55Z

0

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

answered Feb 26, 2017 at 15:05

user7625651

1

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related