1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

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