Hello I have read i can run a query in access from excel but im not having
any luck
This may not be were I in need to post this ? If its not can someone tell me
where to post. The Where is my problem. The SQL below is a access query and I
have a form to pick the between Receiving.RECEIVE_DATE. I would Like to use
a form in Excel to pick the between Dates Can anyone help

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
WHERE (((Receiving.RECEIVE_DATE) Between
[Forms]![frmDatePicker]![txtStartDate] And
[Forms]![frmDatePicker]![txtEndDate]+1)
GROUP BY Receiving.RECEIVE_DATE, Receiving.INVOICE_NUMBER,
Receiving.RPT_GROUP1, Receiving.DEPT_GRP, Receiving.VENDOR_ID,
Sys_Pram.STORE_NAME;

As a simple starting point, have the start and end dates in two cells. Name
the two cells StartDate and End Date then your WHERE code is:

WHERE (((Receiving.RECEIVE_DATE) Between
Format(Range("StartDate"),"'yyyy-mm-dd''") And
Format(Range("EndDate"),"'yyyy-mm-dd'")

Once your code works you can change it to refer to a form, but a form could
still copy the dates to your cells.
--

Rod Gill

"Mike" wrote in message
...
Hello I have read i can run a query in access from excel but im not having
any luck
This may not be were I in need to post this ? If its not can someone tell
me
where to post. The Where is my problem. The SQL below is a access query
and I
have a form to pick the between Receiving.RECEIVE_DATE. I would Like to
use
a form in Excel to pick the between Dates Can anyone help

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
WHERE (((Receiving.RECEIVE_DATE) Between
[Forms]![frmDatePicker]![txtStartDate] And
[Forms]![frmDatePicker]![txtEndDate]+1)
GROUP BY Receiving.RECEIVE_DATE, Receiving.INVOICE_NUMBER,
Receiving.RPT_GROUP1, Receiving.DEPT_GRP, Receiving.VENDOR_ID,
Sys_Pram.STORE_NAME;

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

"Rod Gill" wrote:

As a simple starting point, have the start and end dates in two cells. Name
the two cells StartDate and End Date then your WHERE code is:

WHERE (((Receiving.RECEIVE_DATE) Between
Format(Range("StartDate"),"'yyyy-mm-dd''") And
Format(Range("EndDate"),"'yyyy-mm-dd'")

Once your code works you can change it to refer to a form, but a form could
still copy the dates to your cells.
--

Rod Gill

"Mike" wrote in message
...
Hello I have read i can run a query in access from excel but im not having
any luck
This may not be were I in need to post this ? If its not can someone tell
me
where to post. The Where is my problem. The SQL below is a access query
and I
have a form to pick the between Receiving.RECEIVE_DATE. I would Like to
use
a form in Excel to pick the between Dates Can anyone help

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
WHERE (((Receiving.RECEIVE_DATE) Between
[Forms]![frmDatePicker]![txtStartDate] And
[Forms]![frmDatePicker]![txtEndDate]+1)
GROUP BY Receiving.RECEIVE_DATE, Receiving.INVOICE_NUMBER,
Receiving.RPT_GROUP1, Receiving.DEPT_GRP, Receiving.VENDOR_ID,
Sys_Pram.STORE_NAME;

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

"Rod Gill" wrote:

As a simple starting point, have the start and end dates in two cells. Name
the two cells StartDate and End Date then your WHERE code is:

WHERE (((Receiving.RECEIVE_DATE) Between
Format(Range("StartDate"),"'yyyy-mm-dd''") And
Format(Range("EndDate"),"'yyyy-mm-dd'")

Once your code works you can change it to refer to a form, but a form could
still copy the dates to your cells.
--

Rod Gill

"Mike" wrote in message
...
Hello I have read i can run a query in access from excel but im not having
any luck
This may not be were I in need to post this ? If its not can someone tell
me
where to post. The Where is my problem. The SQL below is a access query
and I
have a form to pick the between Receiving.RECEIVE_DATE. I would Like to
use
a form in Excel to pick the between Dates Can anyone help

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
WHERE (((Receiving.RECEIVE_DATE) Between
[Forms]![frmDatePicker]![txtStartDate] And
[Forms]![frmDatePicker]![txtEndDate]+1)
GROUP BY Receiving.RECEIVE_DATE, Receiving.INVOICE_NUMBER,
Receiving.RPT_GROUP1, Receiving.DEPT_GRP, Receiving.VENDOR_ID,
Sys_Pram.STORE_NAME;

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

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