1 Replies - 7695 Views - Last Post: 10 January 2013 - 03:53 AM Rate Topic: -----

#1 levin  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 24
  • Joined: 08-February 11

how to get data in database between two dates using datepicker

Posted 10 January 2013 - 02:18 AM

Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim dfrom, dto As Date
    
dfrom = DTPicker1.Value
dto = DTPicker2.Value
    
conn.Open "bwais"

rs.Open "select StockNo,NameOfItem,Unit,TDate from history where TDate Between '" & dfrom & "' and '" & dto & "'", conn, 3, 3

                With grid1
                    .ColWidth(0) = 2500
                    .ColWidth(1) = 2050
                    .ColWidth(2) = 1500
                    .ColWidth(3) = 3100
                End With

                    Set grid1.Recordset = rs

Set rs = Nothing
Set conn = Nothing



when i tried to view data between 1/1/2013 to 1/10/2013 the only data displayed was data that has a date of 1/1/2013 and 1/10/2013. Between those dates data are not displayed on the flexgrid.

then when i tried to view again data between 1/1/2013 to 1/8/2013, now all data within that month are displayed on the flexgrid.

i also tried to view data between two different months 1/1/2013 to 2/2/2013, all data save on my database are displayed on the flex grid. including dates even before 1/1/2013 are displayed.

i have tried changing the data type of TDate to Date/Time in the database but it will error to Data type mismatch in criteria expression.

i just want to view data between two different dates..
can anyone give me the correct sql statement for this?

tnx..

i'm trying to upload image but this site says "Error The server returned an error during upload"..

Is This A Good Question/Topic? 0
  • +

Replies To: how to get data in database between two dates using datepicker

#2 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3359
  • View blog
  • Posts: 11,373
  • Joined: 12-December 12

Re: how to get data in database between two dates using datepicker

Posted 10 January 2013 - 03:53 AM

Dim dfrom As Date, dto As Date

..otherwise dfrom will be a Variant.

With Access I had to do the following. It may not be directly relevant (Access uses hashes as date separators); however, you are currently treating the dates as 'strings':

rs.Open "select StockNo,NameOfItem,Unit,TDate from history where TDate Between #" & format(dfrom,"mm/dd/yyyy") & "# and #" & format(dto,"mm/dd/yyyy") & "#", conn, 3, 3

Was This Post Helpful? 1
  • +
  • -

Page 1 of 1