Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO From Excel 2003 to a access.mdb Query
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; |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO From Excel 2003 to a access.mdb Query
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; |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO From Excel 2003 to a access.mdb Query
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; |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO From Excel 2003 to a access.mdb Query
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; |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO From Excel 2003 to a access.mdb Query
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'")"; |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO From Excel 2003 to a access.mdb Query
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'")"; |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO From Excel 2003 to a access.mdb Query
Im playing around with you Example an got to work with Northwind.
I have tried usining in the Between for mine and no luck Could you tell me how this needs to be for the Between date for mine. "Mike" wrote: 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'")"; |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO From Excel 2003 to a access.mdb Query
Give this a go.
Public Sub testread() Dim cnn As ADODB.Connection Dim rs1 As ADODB.Recordset Dim strSQL1 As String, strConn Dim i As Integer Dim mydate1 As String Dim mydate2 As String mydate1 = Sheets(1).Range("H1") mydate2 = Sheets(1).Range("H2") 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 jet strSQL1 = "SELECT LastName, FirstName " _ & "FROM Employees " _ & "WHERE (((HireDate) Between #" & mydate1 & "# " _ & "And #" & mydate2 & "#)) " _ & "ORDER BY LastName; " 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: Im playing around with you Example an got to work with Northwind. I have tried usining in the Between for mine and no luck Could you tell me how this needs to be for the Between date for mine. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO From Excel 2003 to a access.mdb Query
Dave TY VERY MUCH
To use this for what I need all I should need to do is Substitute the select from and where. The other thing I m not sure of would be the (InvAdj.REC_TYPE)=10)) OR (((InvAdj.TIME_STAMP)=15)) ORDER BY InvAdj.TIME_STAMP" _ When in access I had to pass to REC_TYPE=10 Or 15 in the time stamp field to get the right info when closed and went back into the 10 stayed in the time stamp field and the 15 was in the REC_TYPE field IS This going to be a problem But ill try and see what happens "Dave Patrick" wrote: Give this a go. Public Sub testread() Dim cnn As ADODB.Connection Dim rs1 As ADODB.Recordset Dim strSQL1 As String, strConn Dim i As Integer Dim mydate1 As String Dim mydate2 As String mydate1 = Sheets(1).Range("H1") mydate2 = Sheets(1).Range("H2") 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 jet strSQL1 = "SELECT LastName, FirstName " _ & "FROM Employees " _ & "WHERE (((HireDate) Between #" & mydate1 & "# " _ & "And #" & mydate2 & "#)) " _ & "ORDER BY LastName; " 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: Im playing around with you Example an got to work with Northwind. I have tried usining in the Between for mine and no luck Could you tell me how this needs to be for the Between date for mine. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO From Excel 2003 to a access.mdb Query
Hard to say without knowing the data type and function of these columns.
-- Regards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect "Mike" wrote: Dave TY VERY MUCH To use this for what I need all I should need to do is Substitute the select from and where. The other thing I m not sure of would be the (InvAdj.REC_TYPE)=10)) OR (((InvAdj.TIME_STAMP)=15)) ORDER BY InvAdj.TIME_STAMP" _ When in access I had to pass to REC_TYPE=10 Or 15 in the time stamp field to get the right info when closed and went back into the 10 stayed in the time stamp field and the 15 was in the REC_TYPE field IS This going to be a problem But ill try and see what happens |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO From Excel 2003 to a access.mdb Query
Data type is Number And the Fuction is to determine's if it was a Product was
broke down from 1 unit to 10 or if it was broke up from 10 to 1 "Dave Patrick" wrote: Hard to say without knowing the data type and function of these columns. -- Regards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect "Mike" wrote: Dave TY VERY MUCH To use this for what I need all I should need to do is Substitute the select from and where. The other thing I m not sure of would be the (InvAdj.REC_TYPE)=10)) OR (((InvAdj.TIME_STAMP)=15)) ORDER BY InvAdj.TIME_STAMP" _ When in access I had to pass to REC_TYPE=10 Or 15 in the time stamp field to get the right info when closed and went back into the 10 stayed in the time stamp field and the 15 was in the REC_TYPE field IS This going to be a problem But ill try and see what happens |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO From Excel 2003 to a access.mdb Query
Doesn't really mean anything to me. If you need a WHERE clause on these
columns then by all means use them. -- Regards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect "Mike" wrote: Data type is Number And the Fuction is to determine's if it was a Product was broke down from 1 unit to 10 or if it was broke up from 10 to 1 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO From Excel 2003 to a access.mdb Query
Im getting the run time error Extra) in query and its in the where clause
Any ideas Thanks Mike strSQL1 = "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 #" & mydate1 & "# " _ & "And #" & mydate2 & "#+1)) " _ & "And ((InvAdj.REC_TYPE)=10)) " _ & "OR (((InvAdj.TIME_STAMP)=15)) " _ & "ORDER BY InvAdj.TIME_STAMP; " "Dave Patrick" wrote: Doesn't really mean anything to me. If you need a WHERE clause on these columns then by all means use them. -- Regards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect "Mike" wrote: Data type is Number And the Fuction is to determine's if it was a Product was broke down from 1 unit to 10 or if it was broke up from 10 to 1 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO From Excel 2003 to a access.mdb Query
Never Mind I got it
Thanks For all your help "Dave Patrick" wrote: Doesn't really mean anything to me. If you need a WHERE clause on these columns then by all means use them. -- Regards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect "Mike" wrote: Data type is Number And the Fuction is to determine's if it was a Product was broke down from 1 unit to 10 or if it was broke up from 10 to 1 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO From Excel 2003 to a access.mdb Query
Good to hear. You're welcome.
-- Regards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect "Mike" wrote: Never Mind I got it Thanks For all your help |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO From Excel 2003 to a access.mdb Query
This is my almost finished sheet any suggestions to clean up. Two things I
still need is 1.) If the end date less than the start date have msgbox prompting error And 2.) To Sum the amount because the amount changes by days Thanks Mike Private Sub UserForm_Activate() Me.StartDate.Value = Date Me.EndDate.Value = Date End Sub Private Sub CommandButton1_Click() UserForm1.Hide Range("A5").Select ActiveCell.FormulaR1C1 = "DATE" Range("B5").Select ActiveCell.FormulaR1C1 = "UPC_NUM" Range("C5").Select ActiveCell.FormulaR1C1 = "DESCRIPTION" Range("D5").Select ActiveCell.FormulaR1C1 = "RETAIL" Range("E5").Select ActiveCell.FormulaR1C1 = "TOATL RETAIL" Range("E1").Select ActiveCell.FormulaR1C1 = "Start Date" Range("E2").Select ActiveCell.FormulaR1C1 = "End Date" Worksheets("Sheet1").Range("A2").Value = Me.CommandButton1.Caption Worksheets("Sheet1").Range("F1").Value = Me.StartDate.Value Worksheets("Sheet1").Range("F2").Value = Me.EndDate.Value FormatSheet Breakdowns End Sub Public Sub Breakdowns() Dim cnn As ADODB.Connection Dim rs1 As ADODB.Recordset Dim strSQL1 As String, strConn Dim i As Integer Dim ii As Integer Dim mydate1 As String Dim mydate2 As String mydate1 = Sheets(1).Range("F1") mydate2 = Sheets(1).Range("F2") i = 6 ii = 1 'Use for Access (jet) strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=C:\Ilsa\Data\" _ & "Ilsa.mdb;Persist Security Info=False" 'Use for jet strSQL1 = "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 #" & mydate1 & "# " _ & "And #" & mydate2 & "#+1)) " _ & "And ((InvAdj.REC_TYPE)=10) " _ & "OR (((InvAdj.TIME_STAMP)=15)) " _ & "ORDER BY InvAdj.TIME_STAMP; " 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!TIME_STAMP Sheets("Sheet1").Range("B" & i) = rs1!PLU_NUM Sheets("Sheet1").Range("C" & i) = rs1!PLU_DESC Sheets("Sheet1").Range("D" & i) = rs1!LAST_PRICE Sheets("Sheet1").Range("E" & i) = rs1!AMOUNT Sheets("Sheet1").Range("A" & ii) = rs1!STORE_NAME i = i + 1 rs1.MoveNext Loop rs1.Close cnn.Close OpenNewWorkbook End Sub Private Sub OpenNewWorkbook() ' ' Macro2 Macro ' Macro recorded 2/11/2007 by Mike Jones Application.ScreenUpdating = False Columns("A:H").Select 'Cut And Paste into new workbook Selection.Cut Workbooks.Add Range("A1").Select ActiveSheet.Paste FormatSheet 'Private Sub SHEETNAME 'Private Sub Range("A1").Select 'Close template Windows("Breakdowns.xls").Activate Range("A1").Select ActiveWorkbook.Save ActiveWindow.Close End Sub Private Sub FormatSheet() Columns("A:A").ColumnWidth = 16 Columns("A:A").Select Selection.NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@" Columns("B:B").ColumnWidth = 12 Columns("C:C").ColumnWidth = 22.5 Columns("D:F").ColumnWidth = 10 Columns("D:E").Select Selection.NumberFormat = "$#,##0.00" Range("A1").Select End Sub Private Sub SHEETNAME() If Range("A2").Value = "" Then End ActiveSheet.Name = Range("A2").Value End Sub "Dave Patrick" wrote: Good to hear. You're welcome. -- Regards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect "Mike" wrote: Never Mind I got it Thanks For all your help |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO From Excel 2003 to a access.mdb Query
You can put somewhere;
If EndDate < StartDate Then MsgBox "Start Date must be greater than End Date", vbOKOnly, "Error" Exit Sub End If You can use the WorksheetFunction.Sum method. http://www.cpearson.com/excel/optimize.htm http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx You can also use a query to caclulate the sum. SELECT Sum(UnitPrice) AS SumOfUnitPrice FROM Products GROUP BY CategoryID HAVING (((CategoryID)=1)); -- Regards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect "Mike" wrote: This is my almost finished sheet any suggestions to clean up. Two things I still need is 1.) If the end date less than the start date have msgbox prompting error And 2.) To Sum the amount because the amount changes by days Thanks Mike Private Sub UserForm_Activate() Me.StartDate.Value = Date Me.EndDate.Value = Date End Sub Private Sub CommandButton1_Click() UserForm1.Hide Range("A5").Select ActiveCell.FormulaR1C1 = "DATE" Range("B5").Select ActiveCell.FormulaR1C1 = "UPC_NUM" Range("C5").Select ActiveCell.FormulaR1C1 = "DESCRIPTION" Range("D5").Select ActiveCell.FormulaR1C1 = "RETAIL" Range("E5").Select ActiveCell.FormulaR1C1 = "TOATL RETAIL" Range("E1").Select ActiveCell.FormulaR1C1 = "Start Date" Range("E2").Select ActiveCell.FormulaR1C1 = "End Date" Worksheets("Sheet1").Range("A2").Value = Me.CommandButton1.Caption Worksheets("Sheet1").Range("F1").Value = Me.StartDate.Value Worksheets("Sheet1").Range("F2").Value = Me.EndDate.Value FormatSheet Breakdowns End Sub Public Sub Breakdowns() Dim cnn As ADODB.Connection Dim rs1 As ADODB.Recordset Dim strSQL1 As String, strConn Dim i As Integer Dim ii As Integer Dim mydate1 As String Dim mydate2 As String mydate1 = Sheets(1).Range("F1") mydate2 = Sheets(1).Range("F2") i = 6 ii = 1 'Use for Access (jet) strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=C:\Ilsa\Data\" _ & "Ilsa.mdb;Persist Security Info=False" 'Use for jet strSQL1 = "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 #" & mydate1 & "# " _ & "And #" & mydate2 & "#+1)) " _ & "And ((InvAdj.REC_TYPE)=10) " _ & "OR (((InvAdj.TIME_STAMP)=15)) " _ & "ORDER BY InvAdj.TIME_STAMP; " 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!TIME_STAMP Sheets("Sheet1").Range("B" & i) = rs1!PLU_NUM Sheets("Sheet1").Range("C" & i) = rs1!PLU_DESC Sheets("Sheet1").Range("D" & i) = rs1!LAST_PRICE Sheets("Sheet1").Range("E" & i) = rs1!AMOUNT Sheets("Sheet1").Range("A" & ii) = rs1!STORE_NAME i = i + 1 rs1.MoveNext Loop rs1.Close cnn.Close OpenNewWorkbook End Sub Private Sub OpenNewWorkbook() ' ' Macro2 Macro ' Macro recorded 2/11/2007 by Mike Jones Application.ScreenUpdating = False Columns("A:H").Select 'Cut And Paste into new workbook Selection.Cut Workbooks.Add Range("A1").Select ActiveSheet.Paste FormatSheet 'Private Sub SHEETNAME 'Private Sub Range("A1").Select 'Close template Windows("Breakdowns.xls").Activate Range("A1").Select ActiveWorkbook.Save ActiveWindow.Close End Sub Private Sub FormatSheet() Columns("A:A").ColumnWidth = 16 Columns("A:A").Select Selection.NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@" Columns("B:B").ColumnWidth = 12 Columns("C:C").ColumnWidth = 22.5 Columns("D:F").ColumnWidth = 10 Columns("D:E").Select Selection.NumberFormat = "$#,##0.00" Range("A1").Select End Sub Private Sub SHEETNAME() If Range("A2").Value = "" Then End ActiveSheet.Name = Range("A2").Value End Sub |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO From Excel 2003 to a access.mdb Query
TY MR PATRICK
Mike Jones "Dave Patrick" wrote: You can put somewhere; If EndDate < StartDate Then MsgBox "Start Date must be greater than End Date", vbOKOnly, "Error" Exit Sub End If You can use the WorksheetFunction.Sum method. http://www.cpearson.com/excel/optimize.htm http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx You can also use a query to caclulate the sum. SELECT Sum(UnitPrice) AS SumOfUnitPrice FROM Products GROUP BY CategoryID HAVING (((CategoryID)=1)); -- Regards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect "Mike" wrote: This is my almost finished sheet any suggestions to clean up. Two things I still need is 1.) If the end date less than the start date have msgbox prompting error And 2.) To Sum the amount because the amount changes by days Thanks Mike Private Sub UserForm_Activate() Me.StartDate.Value = Date Me.EndDate.Value = Date End Sub Private Sub CommandButton1_Click() UserForm1.Hide Range("A5").Select ActiveCell.FormulaR1C1 = "DATE" Range("B5").Select ActiveCell.FormulaR1C1 = "UPC_NUM" Range("C5").Select ActiveCell.FormulaR1C1 = "DESCRIPTION" Range("D5").Select ActiveCell.FormulaR1C1 = "RETAIL" Range("E5").Select ActiveCell.FormulaR1C1 = "TOATL RETAIL" Range("E1").Select ActiveCell.FormulaR1C1 = "Start Date" Range("E2").Select ActiveCell.FormulaR1C1 = "End Date" Worksheets("Sheet1").Range("A2").Value = Me.CommandButton1.Caption Worksheets("Sheet1").Range("F1").Value = Me.StartDate.Value Worksheets("Sheet1").Range("F2").Value = Me.EndDate.Value FormatSheet Breakdowns End Sub Public Sub Breakdowns() Dim cnn As ADODB.Connection Dim rs1 As ADODB.Recordset Dim strSQL1 As String, strConn Dim i As Integer Dim ii As Integer Dim mydate1 As String Dim mydate2 As String mydate1 = Sheets(1).Range("F1") mydate2 = Sheets(1).Range("F2") i = 6 ii = 1 'Use for Access (jet) strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=C:\Ilsa\Data\" _ & "Ilsa.mdb;Persist Security Info=False" 'Use for jet strSQL1 = "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 #" & mydate1 & "# " _ & "And #" & mydate2 & "#+1)) " _ & "And ((InvAdj.REC_TYPE)=10) " _ & "OR (((InvAdj.TIME_STAMP)=15)) " _ & "ORDER BY InvAdj.TIME_STAMP; " 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!TIME_STAMP Sheets("Sheet1").Range("B" & i) = rs1!PLU_NUM Sheets("Sheet1").Range("C" & i) = rs1!PLU_DESC Sheets("Sheet1").Range("D" & i) = rs1!LAST_PRICE Sheets("Sheet1").Range("E" & i) = rs1!AMOUNT Sheets("Sheet1").Range("A" & ii) = rs1!STORE_NAME i = i + 1 rs1.MoveNext Loop rs1.Close cnn.Close OpenNewWorkbook End Sub Private Sub OpenNewWorkbook() ' ' Macro2 Macro ' Macro recorded 2/11/2007 by Mike Jones Application.ScreenUpdating = False Columns("A:H").Select 'Cut And Paste into new workbook Selection.Cut Workbooks.Add Range("A1").Select ActiveSheet.Paste FormatSheet 'Private Sub SHEETNAME 'Private Sub Range("A1").Select 'Close template Windows("Breakdowns.xls").Activate Range("A1").Select ActiveWorkbook.Save ActiveWindow.Close End Sub Private Sub FormatSheet() Columns("A:A").ColumnWidth = 16 Columns("A:A").Select Selection.NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@" Columns("B:B").ColumnWidth = 12 Columns("C:C").ColumnWidth = 22.5 Columns("D:F").ColumnWidth = 10 Columns("D:E").Select Selection.NumberFormat = "$#,##0.00" Range("A1").Select End Sub Private Sub SHEETNAME() If Range("A2").Value = "" Then End ActiveSheet.Name = Range("A2").Value End Sub |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO From Excel 2003 to a access.mdb Query
You're welcome.
-- Regards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect "Mike" wrote: TY MR PATRICK Mike Jones |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 query from Access 2000 database | Excel Worksheet Functions | |||
importing/linking data from an Access 2003 Query to an Excel 2003 | Excel Discussion (Misc queries) | |||
MS Query - from Access 2003 to Excel 97 | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) | |||
How To Pull Access Query into Excel (2003): Reposting | Excel Programming |