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

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

 

Code:

SELECT * FROM sold WHERE dateOfSold=#1/1/2006#

 

Thank you, for ur reply. I it is very usiful to me.

 

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

 

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

 

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 & "#"

 

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