run-time error '-2147217900 (80040e14)

Question

tuyudi 0 Newbie Poster

14 Years Ago

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

mssql

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

2 Contributors
1 Reply
171 Views
13 Hours Discussion Span
Latest Post 14 Years Ago Latest Post by sknake

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

sknake 1,622 Senior Poster Featured Poster · Answer 1 · 2009-12-13T16:42:55+00:00

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