Much less confusion if you always pass them in as strings.
Public Sub testread()
Dim cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim strSQL1 As String, strConn
Dim i As Integer
Dim mydate As String
mydate = Sheets(1).Range("H1")
i = 1
'Use for Access (jet)
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\Program Files\Microsoft Office\" _
& "Office12\SAMPLES\Northwind.mdb;Persist Security Info=False"
'Use for SQL server (OLEDB)
'strConn = "Provider=SQLOLEDB.1;" _
& "Integrated Security=SSPI;" _
& "Persist Security Info=False;" _
& "Initial Catalog=NorthWind;" _
& "Data Source=PE1600"
'Use for jet
strSQL1 = "SELECT LastName, FirstName " _
& "FROM Employees " _
& "WHERE (((HireDate)#" & mydate & "#)); "
'Use for SQL Server
'strSQL1 = "SELECT LastName, FirstName " _
& "FROM Employees " _
& "WHERE (HireDate CONVERT(DATETIME, '" & mydate & "', 102)) "
Set cnn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
cnn.Open strConn
rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly
Do While rs1.EOF = False
Sheets("Sheet1").Range("A" & i) = rs1!LastName
Sheets("Sheet1").Range("B" & i) = rs1!FirstName
i = i + 1
rs1.MoveNext
Loop
rs1.Close
cnn.Close
End Sub
--
Regards,
Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Mike" wrote:
Well know It Highlights the StartDate
"Mike" wrote:
Ron Thanks for the suggestion but I can not get rid of the syntax error
Maybe you have another Idea if not thanks anyway. This my Code in Excel
and it Highlights the Between part of the code
stSQL = "SELECT DISTINCTROW Receiving.RECEIVE_DATE,
Receiving.INVOICE_NUMBER, Receiving.RPT_GROUP1, Receiving.DEPT_GRP,
Sum(Receiving.COST) AS [Sum Of COST], Receiving.VENDOR_ID,
Sys_Pram.STORE_NAME FROM Receiving, Sys_Pram GROUP BY
Receiving.RECEIVE_DATE,
Receiving.INVOICE_NUMBER, Receiving.RPT_GROUP1, Receiving.DEPT_GRP,
Receiving.VENDOR_ID, Sys_Pram.STORE_NAME"
WHERE (((Receiving.RECEIVE_DATE) Between
Format(Range("StartDate"),"'yyyy-mm-dd''") And
Format(Range("EndDate"),"'yyyy-mm-dd'")";