ADO From Excel 2003 to a access.mdb Query

M

Mike

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;
 
R

Rod Gill

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.
 
M

Mike

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 said:
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 said:
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;
Click to expand...
Click to expand...
 
M

Mike

Well know It Highlights the StartDate

Mike said:
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 said:
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 said:
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;
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Patrick

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 said:
Well know It Highlights the StartDate

Mike said:
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'")";
Click to expand...
Click to expand...
 
M

Mike

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 said:
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 said:
Well know It Highlights the StartDate

Mike said:
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'")";
Click to expand...
Click to expand...
Click to expand...
 
M

Mike

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 said:
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 said:
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 said:
Well know It Highlights the StartDate

:

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'")";
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Patrick

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
 
M

Mike

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 said:
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 said:
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.
Click to expand...
Click to expand...
 
M

Mike

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 said:
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 said:
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
Click to expand...
Click to expand...
 
D

Dave Patrick

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
 
M

Mike

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 said:
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 said:
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
Click to expand...
Click to expand...
 
M

Mike

Never Mind I got it

Thanks For all your help

Dave Patrick said:
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 said:
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
Click to expand...
Click to expand...
 
M

Mike

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 said:
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 said:
Never Mind I got it

Thanks For all your help
Click to expand...
Click to expand...
 
D

Dave Patrick

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/library/aa139976(office.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 said:
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
Click to expand...
 
M

Mike

TY MR PATRICK

Mike Jones

Dave Patrick said:
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/library/aa139976(office.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 said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Having trouble with ADO to run a access query From Excel 1
Problem With Parameters in Cross Tab Query 0
Union Query Edit 1
Parameter Criteria from a forms Text box - 2003 - not working 5
Running Update Query in VBA fails 5
Using ADO to Excel with this Query will not work? 1
Characters found at end of sql error-Repost 2
Duplicate values in Crosstab 3

Top