help with passing date to access as parameter query(code included)

[Guest]

The code below does not seem to work and I was wondering if anyone could tell
me why. The last line fails when I try to open the recordset.

Your help is appreciated.

Dim i As Long
Dim lngMove As Long
Dim strDateCheck As String
Dim strCreationData As String
Dim strEditDate As String
Dim sDate As String

Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long, c As Long

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.Oledb.4.0; " & _
"Data Source=\\Gresnt07\Complaints\BreakManager.mdb;"

Set rs = New ADODB.Recordset
sDate = Sheet1.Cells(2, 1).Value
rs.Open "Select tblBreaks.BrkDate, tblBreaks.BrkTime, tblBreaks.brkCrew FROM
tblBreaks WHERE tblBreaks.BrkDate = sDate, cn, adOpenDynamic,
adLockOptimistic"

 

[Guest]

Vince

look at the last few lines below. I think you were not properly handling the
date.

Set rs = New ADODB.Recordset
' need to check the format of the date and the date format in the database
sDate = "#" & Format(Sheet1.Cells(2, 1).Value, "mm/dd/yyyy") & "#"
rs.Open "Select tblBreaks.BrkDate, tblBreaks.BrkTime, " & _
"tblBreaks.brkCrew " & _
"FROM tblBreaks " & _
"WHERE tblBreaks.BrkDate = " & sDate, _
cn, adOpenDynamic, adLockOptimistic

 

[Guest]

Thank you, that worked great and is exactly what I needed.