3 Replies - 793 Views - Last Post: 03 November 2012 - 09:46 PM Rate Topic: -----

#1 shiban  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 29-October 12

searching between dates

Posted 29 October 2012 - 10:32 AM

In my project i am trying to search data between two dates by DTPicker
my codes are
Dim sSQL As String
sSQL = "select * from ExpCM Where Format(Dat, 'dd-mm-yyyy') Between '" & Format(DTPicker1.Value, "dd-mm-yyyy") & "' And '" & Format(DTPicker2.Value, "dd-mm-yyyy") & "'"
Debug.Print sSQL
CMStock.Adodc1.RecordSource = sSQL


Although its working, it is giving vague results like in my database i ve data with the following dates in the following format
25-09-2012
25-09-2012
30-09-2012
25-10-2012
25-10-2012
28-10-2012
but when i am searching between dates like 25-10-2012 to 29-10-2012 am getting three results(25-10-2012,25-10-2012,28-10-2012)
but when i am searching between dates like 24-10-2012 to 29-10-2012 am getting 5 results
25-09-2012
25-09-2012
25-10-2012
25-10-2012
28-10-2012
how is this possible. please help. its urgent

This post has been edited by modi123_1: 29 October 2012 - 11:43 AM
Reason for edit:: highlight the text and just click the 'code' button in the text editor


Is This A Good Question/Topic? 0
  • +

Replies To: searching between dates

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9094
  • View blog
  • Posts: 34,147
  • Joined: 12-June 08

Re: searching between dates

Posted 29 October 2012 - 11:45 AM

Which sql is this for? More than likely you are jacking up the dates formatting and this is being evaluated as strings.
Was This Post Helpful? 0
  • +
  • -

#3 ramon29  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 30-October 12

Re: searching between dates

Posted 30 October 2012 - 01:03 AM

View Postshiban, on 29 October 2012 - 10:32 AM, said:

In my project i am trying to search data between two dates by DTPicker
my codes are
Dim sSQL As String
sSQL = "select * from ExpCM Where Format(Dat, 'dd-mm-yyyy') Between '" & Format(DTPicker1.Value, "dd-mm-yyyy") & "' And '" & Format(DTPicker2.Value, "dd-mm-yyyy") & "'"
Debug.Print sSQL
CMStock.Adodc1.RecordSource = sSQL


Although its working, it is giving vague results like in my database i ve data with the following dates in the following format
25-09-2012
25-09-2012
30-09-2012
25-10-2012
25-10-2012
28-10-2012
but when i am searching between dates like 25-10-2012 to 29-10-2012 am getting three results(25-10-2012,25-10-2012,28-10-2012)
but when i am searching between dates like 24-10-2012 to 29-10-2012 am getting 5 results
25-09-2012
25-09-2012
25-10-2012
25-10-2012
28-10-2012
how is this possible. please help. its urgent




it seems problem is the formatting of datefield in the database. It maybe formatted as string or text. If it is, you need to convert it to date in your SQL query.

sSQL="Select * from ExpCM Where CDate(Datefield) between date1 and date1
Was This Post Helpful? 0
  • +
  • -

#4 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: searching between dates

Posted 03 November 2012 - 09:46 PM

the examples you've given are consistent with your data being evaluated as strings. Imagine if you sorted all the examples as strings, which ones would fall between the values you have given. They would come out as you describe.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1