Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with passing date to access as parameter query(code included)
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" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with passing date to access as parameter query(code included)
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 -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "Vince" wrote: 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" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with passing date to access as parameter query(code inclu
Thank you, that worked great and is exactly what I needed.
"Martin Fishlock" wrote: 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 -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "Vince" wrote: 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" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pass parameter to access query | Links and Linking in Excel | |||
Passing parameter to a select query | Excel Programming | |||
passing parameter to a ms query | Excel Programming | |||
Passing parameter to a query | Excel Programming | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |