0

Please see and find out what was the problem. It's seem OK on me but it doesn't work on vb code using SQL Server 2000

"SELECT qry_close_month.Item_code, T_ITEM.item_name, " & _
            "Sum(IIf(qry_close_month.Bulan='January',qry_close_month.Stock,0)) AS Jan, " & _
            "Sum(IIf(qry_close_month.Bulan='February',qry_close_month.Stock,0)) AS Feb, " & _
            "Sum(IIf(qry_close_month.Bulan='March',qry_close_month.Stock,0)) AS Mar, " & _
            "Sum(IIf(qry_close_month.Bulan='April',qry_close_month.Stock,0)) AS Apr, " & _
            "Sum(IIf(qry_close_month.Bulan='May',qry_close_month.Stock,0)) AS May, " & _
            "Sum(IIf(qry_close_month.Bulan='June',qry_close_month.Stock,0)) AS Jun, " & _
            "Sum(IIf(qry_close_month.Bulan='July,qry_close_month.Stock,0)) AS Jul, " & _
            "Sum(IIf(qry_close_month.Bulan='August',qry_close_month.Stock,0)) AS Aug, " & _
            "Sum(IIf(qry_close_month.Bulan='September',qry_close_month.Stock,0)) AS Sep, " & _
            "Sum(IIf(qry_close_month.Bulan='October',qry_close_month.Stock,0)) AS Oct, " & _
            "Sum(IIf(qry_close_month.Bulan='November',qry_close_month.Stock,0)) AS Nov, " & _
            "Sum(IIf(qry_close_month.Bulan='December',qry_close_month.Stock,0)) AS Dec, " & _
            "qry_close_month.Bulan, qry_close_month.Tahun " & _
            "FROM qry_close_month INNER JOIN T_ITEM ON qry_close_month.Item_code = T_ITEM.item_code " & _
            "GROUP BY qry_close_month.Item_code, T_ITEM.item_name, qry_close_month.Bulan, qry_close_month.Tahun " & _
            "WHERE TAHUN='" & year(date) & "'"

Edited by peter_budo: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)

2
Contributors
1
Reply
2
Views
7 Years
Discussion Span
Last Post by sknake
0

Please use code tags when posting code on daniweb, and post the RAW sql -- not a huge concatenated string from VB:

The problem is that you forgot an ' in your query on the line for July:

SELECT qry_close_month.Item_code, T_ITEM.item_name,
Sum(IIf(qry_close_month.Bulan='January',qry_close_month.Stock,0)) AS Jan,
Sum(IIf(qry_close_month.Bulan='February',qry_close_month.Stock,0)) AS Feb,
Sum(IIf(qry_close_month.Bulan='March',qry_close_month.Stock,0)) AS Mar,
Sum(IIf(qry_close_month.Bulan='April',qry_close_month.Stock,0)) AS Apr,
Sum(IIf(qry_close_month.Bulan='May',qry_close_month.Stock,0)) AS May,
Sum(IIf(qry_close_month.Bulan='June',qry_close_month.Stock,0)) AS Jun,
Sum(IIf(qry_close_month.Bulan='July,qry_close_month.Stock,0)) AS Jul,
Sum(IIf(qry_close_month.Bulan='August',qry_close_month.Stock,0)) AS Aug,
Sum(IIf(qry_close_month.Bulan='September',qry_close_month.Stock,0)) AS Sep,
Sum(IIf(qry_close_month.Bulan='October',qry_close_month.Stock,0)) AS Oct,
Sum(IIf(qry_close_month.Bulan='November',qry_close_month.Stock,0)) AS Nov,
Sum(IIf(qry_close_month.Bulan='December',qry_close_month.Stock,0)) AS Dec,
qry_close_month.Bulan, qry_close_month.Tahun
FROM qry_close_month INNER JOIN T_ITEM ON qry_close_month.Item_code = T_ITEM.item_code
GROUP BY qry_close_month.Item_code, T_ITEM.item_name, qry_close_month.Bulan, qry_close_month.Tahun
WHERE TAHUN='2003' 

Line #8 is missing the '. This should fix your problem:

"SELECT qry_close_month.Item_code, T_ITEM.item_name, " & _
"Sum(IIf(qry_close_month.Bulan='January',qry_close_month.Stock,0)) AS Jan, " & _
"Sum(IIf(qry_close_month.Bulan='February',qry_close_month.Stock,0)) AS Feb, " & _
"Sum(IIf(qry_close_month.Bulan='March',qry_close_month.Stock,0)) AS Mar, " & _
"Sum(IIf(qry_close_month.Bulan='April',qry_close_month.Stock,0)) AS Apr, " & _
"Sum(IIf(qry_close_month.Bulan='May',qry_close_month.Stock,0)) AS May, " & _
"Sum(IIf(qry_close_month.Bulan='June',qry_close_month.Stock,0)) AS Jun, " & _
"Sum(IIf(qry_close_month.Bulan='July',qry_close_month.Stock,0)) AS Jul, " & _
"Sum(IIf(qry_close_month.Bulan='August',qry_close_month.Stock,0)) AS Aug, " & _
"Sum(IIf(qry_close_month.Bulan='September',qry_close_month.Stock,0)) AS Sep, " & _
"Sum(IIf(qry_close_month.Bulan='October',qry_close_month.Stock,0)) AS Oct, " & _
"Sum(IIf(qry_close_month.Bulan='November',qry_close_month.Stock,0)) AS Nov, " & _
"Sum(IIf(qry_close_month.Bulan='December',qry_close_month.Stock,0)) AS Dec, " & _
"qry_close_month.Bulan, qry_close_month.Tahun " & _
"FROM qry_close_month INNER JOIN T_ITEM ON qry_close_month.Item_code = T_ITEM.item_code " & _
"GROUP BY qry_close_month.Item_code, T_ITEM.item_name, qry_close_month.Bulan, qry_close_month.Tahun " & _
"WHERE TAHUN='" & year(date) & "'" 

Edited by happygeek: fixed formatting

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.