TY Dave,

Im not really good at this but I spend 24hrs a day playing around. The
Formula i got below is what Access query SQL gave me and it works. I would
like to be able to pick the Between dates from a Excel form. I have read your
response and Don't really understand. Do you think you could help with what i
have or take what I have and format it the way your talking about.

If you need more info or I need to explane more Please let me know

Thanks Again
Mike

Private Sub BreakdownsTemplate()
'
' Breakdowns
' Macro recorded 2/11/2007 by Mike Jones
'

'
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data
Source=C:\ILSA\data\ReportBuilder.mdb;Mode=Share Deny None;Extended Properti"
_
, _
"es="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLED"
_
, _
"B:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk
Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System
Data" _
, _
"base=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy
Locale on Compact=False;Jet OLEDB:Compact Without Replica Repai" _
, "r=False;Jet OLEDB:SFP=False"), Destination:=Range("A7"))
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT InvAdj.TIME_STAMP, InvAdj.PLU_NUM, PLU.PLU_DESC , InvAdj.QTY,
PLU.LAST_PRICE, [QTY]*[LAST_PRICE] AS AMOUNT, Sys_Pram.STORE_NAME FROM
Sys_Pram, InvAdj INNER JOIN PLU ON InvAdj.PLU_NUM =PLU.PLU_NUM
WHERE(((InvAdj.Time_STAMP" _
, _
") Between #12/27/2006# And #12/27/2006#+1) And
((InvAdj.REC_TYPE)=10)) OR (((InvAdj.TIME_STAMP)=15)) ORDER BY
InvAdj.TIME_STAMP" _
)
.Name = "+Connect to New Data Source_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = _
"C:\Documents and Settings\Mike Jones\My Documents\My Data
Sources\BreakdownsTest.odc"
.Refresh BackgroundQuery:=False
End With
End Sub

"Dave Patrick" wrote:

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'")";