1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

How to use date as a condition in where clause of access Sql

Discussion in 'MS Access' started by nadunalexander, Apr 17, 2007.

  1. nadunalexander

    nadunalexander New Member

    Joined:
    Sep 13, 2006
    Messages:
    34
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Student
    sqlStr1 = "SELECT * FROM sold WHERE dateOfSold=" & "3/29/2007"

    I used this statement to retrive all the data they have value 3/29/2007 in the field of dateOfSold. When i try this statement this return empty record set. dateOfSold field is in access table sales. fieldtype is date/time. plese help me
     
  2. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,646
    Likes Received:
    86
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    Code:
    SELECT * FROM sold WHERE dateOfSold=#1/1/2006#
     
    1 person likes this.
  3. nadunalexander

    nadunalexander New Member

    Joined:
    Sep 13, 2006
    Messages:
    34
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Student
    Thank you, for ur reply. I it is very usiful to me.
     
  4. abstrused00388

    abstrused00388 New Member

    Joined:
    Jun 29, 2011
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    This code was very much helpful i just modified it as :

    Adodc1.RecordSource = "SELECT * FROM cm1 WHERE dt>=#1/1/2011 02:00:00 AM# AND dt<=#4/1/2011 02:00:00 PM#"

    for searching a database using VB !

    please let me know if there is a way to accept input from the user in a text box and use thm instead of the constant values that i have used
     
  5. mvbsearch

    mvbsearch New Member

    Joined:
    Aug 28, 2009
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Teacher
    Location:
    TUNISIA
    It can be as following :
    Code:
    Adodc1.RecordSource = "SELECT * FROM cm1 WHERE dt>=# " & Begin_Date.Text & "# AND dt<=# " & End_Date.Text & "#"
    where Begin_Date and En_Date are 2 textboxes you must type in the 2 dates for the search criteria
     
  6. mvbsearch

    mvbsearch New Member

    Joined:
    Aug 28, 2009
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Teacher
    Location:
    TUNISIA
    Oh sorry for invalid space in my previous post, It must be :
    Code:
    Adodc1.RecordSource = "SELECT * FROM cm1 WHERE [B]dt>=#"[/B] & Begin_Date.Text & "# AND [B]dt<=#"[/B] & End_Date.Text & "#"
     
  7. mialuzzatto

    mialuzzatto New Member

    Joined:
    Aug 5, 2015
    Messages:
    122
    Likes Received:
    4
    Trophy Points:
    0
    Occupation:
    Work at https://www.idevelopersquare.com
    Location:
    Waltham, MA, USA
    Home Page:
    Hello,

    Use Date function in where clause....
    Code:
    Sub CreateRst_WithSQL()
       Dim conn As ADODB.Connection
       Dim myRecordset As ADODB.Recordset
       Dim strConn As String
    
       strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & CurrentProject.Path & _
          "\mydb.mdb"
    
       Set conn = New ADODB.Connection
       conn.Open strConn
    
       Set myRecordset = conn.Execute("SELECT * FROM Employees WHERE ((Year([HireDate])<1993) OR (City='Redmond'))      ")
       Do Until myRecordset.EOF
           For Each fld In myRecordset.Fields
              Debug.Print fld.Name & "=" & fld.Value
           Next fld
           myRecordset.MoveNext
       Loop
    
       
       myRecordset.Close
       Set myRecordset = Nothing
       conn.Close
       Set conn = Nothing
    End Sub
     
    Last edited by a moderator: Nov 24, 2015

Share This Page