jmsienrique
asked on
EXCEL (SQL SENTENCE TO VBA)
Hello Experts, I did a Connection to Excel Sheet and AS/400 Databese with VBA and the main purpose is to get the summary by dates, the codes works well, but when I add an other field (this is the field that I add "FKITSAVE.TSPN" is the part Nr), the Macro send a mistake to ODBC, I want to know the quantity issued by part, but just the summary.
AS/400 Databese has:
Part Nr Qty Issued Date
A1 100 01/01/2010
A1 106 02/01/2010
A1 109 03/01/2010
A1 100 04/01/2010
A1 108 05/01/2010
The summary that I need is:
Part Nr Qty Issued
A1 523
This Is my CODE:
Sub IssKBM()
Dim qryTable As QueryTable
Dim rngDestination As Range
Dim strConnection As String
Dim strSQL As String
Dim tpe As String
'Clean File
Sheets("Issues").Select
Range("A1").Select
Selection.CurrentRegion.Se lect
Selection.EntireColumn.Del ete
Range("A1").Select
'Connection to AS/400 (KBM)
strConnection = "ODBC;DSN=AS/400-2;"
Set rngDestination = ActiveSheet.Range("A1")
'Start SQL
strSQL = "SELECT FKITSAVE.TSPN, SUM(FKITSAVE.TSTQTY)" & Chr(13) & "" & Chr(10) & _
"FROM S10663DC.KBM500MFG.FKITSAV E FKITSAVE" & Chr(13) & "" & Chr(10) & _
"WHERE (FKITSAVE.TSCO=001) AND (FKITSAVE.TSDATE>1091201) AND (FKITSAVE.TSCODE='13')" & Chr(13) & "" & Chr(10) & _
"ORDER BY FKITSAVE.TSPN"
'Start Qry
Set qryTable = ActiveSheet.QueryTables.Ad d(strConne ction, rngDestination)
qryTable.CommandText = strSQL
qryTable.Refresh False
End Sub
I hope so someone can help me :(
AS/400 Databese has:
Part Nr Qty Issued Date
A1 100 01/01/2010
A1 106 02/01/2010
A1 109 03/01/2010
A1 100 04/01/2010
A1 108 05/01/2010
The summary that I need is:
Part Nr Qty Issued
A1 523
This Is my CODE:
Sub IssKBM()
Dim qryTable As QueryTable
Dim rngDestination As Range
Dim strConnection As String
Dim strSQL As String
Dim tpe As String
'Clean File
Sheets("Issues").Select
Range("A1").Select
Selection.CurrentRegion.Se
Selection.EntireColumn.Del
Range("A1").Select
'Connection to AS/400 (KBM)
strConnection = "ODBC;DSN=AS/400-2;"
Set rngDestination = ActiveSheet.Range("A1")
'Start SQL
strSQL = "SELECT FKITSAVE.TSPN, SUM(FKITSAVE.TSTQTY)" & Chr(13) & "" & Chr(10) & _
"FROM S10663DC.KBM500MFG.FKITSAV
"WHERE (FKITSAVE.TSCO=001) AND (FKITSAVE.TSDATE>1091201) AND (FKITSAVE.TSCODE='13')" & Chr(13) & "" & Chr(10) & _
"ORDER BY FKITSAVE.TSPN"
'Start Qry
Set qryTable = ActiveSheet.QueryTables.Ad
qryTable.CommandText = strSQL
qryTable.Refresh False
End Sub
I hope so someone can help me :(
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hello Rory thanks, but It doesn't work, maybe I don't undestand, I need to add Group by and the new code, would be like this:
strSQL = "SELECT GROUP BY(FKITSAVE.TSPN), SUM(FKITSAVE.TSTQTY)" & Chr(13) & "" & Chr(10) & _
"FROM S10663DC.KBM500MFG.FKITSAV E FKITSAVE" & Chr(13) & "" & Chr(10) & _
"WHERE (FKITSAVE.TSCO=001) AND (FKITSAVE.TSDATE>1090101) AND (FKITSAVE.TSCODE='13')" & Chr(13) & "" & Chr(10) & _
"ORDER BY FKITSAVE.TSPN"
strSQL = "SELECT GROUP BY(FKITSAVE.TSPN), SUM(FKITSAVE.TSTQTY)" & Chr(13) & "" & Chr(10) & _
"FROM S10663DC.KBM500MFG.FKITSAV
"WHERE (FKITSAVE.TSCO=001) AND (FKITSAVE.TSDATE>1090101) AND (FKITSAVE.TSCODE='13')" & Chr(13) & "" & Chr(10) & _
"ORDER BY FKITSAVE.TSPN"
Nope:
strSQL = "SELECT FKITSAVE.TSPN, SUM(FKITSAVE.TSTQTY) As Qty " & Chr(13) & "" & Chr(10) & _
"FROM S10663DC.KBM500MFG.FKITSAV E FKITSAVE" & Chr(13) & "" & Chr(10) & _
"WHERE (FKITSAVE.TSCO=001) AND (FKITSAVE.TSDATE>1090101) AND (FKITSAVE.TSCODE='13')" & Chr(13) & "" & Chr(10) & _
"GROUP BY(FKITSAVE.TSPN) ORDER BY FKITSAVE.TSPN"
strSQL = "SELECT FKITSAVE.TSPN, SUM(FKITSAVE.TSTQTY) As Qty " & Chr(13) & "" & Chr(10) & _
"FROM S10663DC.KBM500MFG.FKITSAV
"WHERE (FKITSAVE.TSCO=001) AND (FKITSAVE.TSDATE>1090101) AND (FKITSAVE.TSCODE='13')" & Chr(13) & "" & Chr(10) & _
"GROUP BY(FKITSAVE.TSPN) ORDER BY FKITSAVE.TSPN"
Try this:
strSQL = "SELECT FKITSAVE.TSPN, SUM(FKITSAVE.TSTQTY) As Qty " & _
"FROM S10663DC.KBM500MFG.FKITSAV E FKITSAVE " & _
"WHERE (FKITSAVE.TSCO=001) AND (FKITSAVE.TSCODE='13') " & _
"GROUP BY(FKITSAVE.TSPN) " & _
"HAVING (FKITSAVE.TSDATE > 1090101) "
Leon
strSQL = "SELECT FKITSAVE.TSPN, SUM(FKITSAVE.TSTQTY) As Qty " & _
"FROM S10663DC.KBM500MFG.FKITSAV
"WHERE (FKITSAVE.TSCO=001) AND (FKITSAVE.TSCODE='13') " & _
"GROUP BY(FKITSAVE.TSPN) " & _
"HAVING (FKITSAVE.TSDATE > 1090101) "
Leon
On second thought, what is "1090101" and where did it come from?
Leon
Leon
Like I said earlier what's the date formatted like... making sure the SQL runs first will make sure to eliminate problems.
What error are you getting?
NG,
What error are you getting?
NG,
ASKER
Thanks Rory and Pcelba, Just a question there is a Link or Book in order to learn SQL? Thanks again
Yes, the tutorial is e.g. here: http://www.w3schools.com/SQl/sql_select.asp or here: http://www.sql-tutorial.net/
leonstryker, the AS400 date is a little bit non-standard for others... See e.g. here: http://www.code400.com/forum/showthread.php/2921-Strange-date-in-AS400
I was actually confused that the original question had 1091201 and then somewhere along the way it got changed to 1090101
Leon
Leon
ASKER
leonstryker: thanks to ask me, the information was the date that I want to search, before to see the answer I tryed to found the solution and move the date and it means 1090101, this represents 1 to century, 09 the year, 01 month, and 01 day, sorry if I wasn't clear, and thanks to all answer
Maybe something like?
Sql = " SELECT FKITSAVE.tspn, Sum(FKITSAVE.tstqty) AS SumOftstqty, FKITSAVE.tscode, FKITSAVE.tsdate"
Sql = Sql & " FROM FKITSAVE"
Sql = Sql & " GROUP BY FKITSAVE.tspn, FKITSAVE.tscode, FKITSAVE.tsdate"
Sql = Sql & " WHERE (((FKITSAVE.tspn)="A1") AND ((FKITSAVE.tscode)="13") AND ((FKITSAVE.tsdate)>#1/1/20
NG,