Dim sql_search As String
Dim search As String
Dim search2 As String
search = CBContractor.Text
search2 = TXTItem.Text
Dim num As Integer
If search = "ALL " Then
If search2 = "" Then
num = 0
Else
num = 2
End If
Else
If search = "" Then
If search2 = "" Then
num = 0
Else
num = 1
End If
Else
If search2 = "" Then
num = 2
Else
num = 3
End If
End If
End If
' sql_search = " SELECT IT_PlannedList.PlannedListID, IT_Description.Item_Desc AS Item, IT_PlannedList.Remark, Contractor.Contractor_Name AS CT, IT_PlannedList.OrderedTimes, IT_PlannedList.Qty,SUM(IT_TransContractor.ReceivedQty) as RecQty , IT_PlannedList.Qty - sum(IT_TransContractor.ReceivedQty) AS [Awaiting Qty], IT_PlannedList.Unit FROM (((Contractor INNER JOIN IT_PlannedList ON Contractor.Contractor_ID = IT_PlannedList.ContractorID) INNER JOIN IT_TransContractor ON IT_PlannedList.ItemNo = IT_TransContractor.ItemNo AND IT_PlannedList.ContractorID = IT_TransContractor.ContractorID AND IT_PlannedList.OrderedTimes = IT_TransContractor.OrderedTimes) INNER JOIN IT_Description ON IT_TransContractor.ItemNo = IT_Description.ItemNo) "
sql_search = " SELECT IT_PlannedList.PlannedListID, IT_PlannedList.ItemNo AS [Item No], IT_Description.Item_Desc AS Item, IT_PlannedList.ContractorID, Contractor.Contractor_Name AS CT, IT_PlannedList.OrderedTimes, IT_PlannedList.Qty, SUM([IT_TransContractor.ReceivedQty]) AS [Rec Qty], IT_PlannedList.Qty - Sum([IT_TransContractor.ReceivedQty]) AS [Awaiting Qty], IT_PlannedList.Unit, IT_TransContractor.ModelID, IT_PlannedList.DateCreated FROM Contractor INNER JOIN ((IT_Description INNER JOIN IT_PlannedList ON IT_Description.ItemNo = IT_PlannedList.ItemNo) LEFT JOIN IT_TransContractor ON (IT_PlannedList.ContractorID = IT_TransContractor.ContractorID) AND (IT_PlannedList.ItemNo = IT_TransContractor.ItemNo) AND (IT_PlannedList.PlannedListID = IT_TransContractor.PlannedListID)) ON Contractor.Contractor_ID = IT_PlannedList.ContractorID "
Dim sqlWhere As String = ""
Select Case num
Case 0 : sqlWhere = ""
Case 1 : sqlWhere = "where Item_Desc LIKE'" & search2 & "'"
Case 2 : sqlWhere = "where Contractor_Name LIKE '" & search & "' "
Case 3 : sqlWhere = "where Contractor_Name LIKE '" & search & "' and Item_Desc LIKE'" & search2 & "'"
End Select
Dim sqlemp As String
Dim group As String = "GROUP BY IT_PlannedList.PlannedListID, IT_PlannedList.ItemNo, IT_Description.Item_Desc, IT_PlannedList.ContractorID, Contractor.Contractor_Name, IT_PlannedList.OrderedTimes, IT_PlannedList.Qty, IT_PlannedList.Unit, IT_TransContractor.ModelID, IT_PlannedList.DateCreated ORDER BY IT_PlannedList.ItemNo"
sqlemp = sql_search & sqlWhere & group
' MessageBox.Show(num)
da = New OleDbDataAdapter(sqlemp, conn)
da.Fill(ds, "Contractor")
If ds.Tables("Contractor").Rows.Count <> 0 Then
With DataGridView1
.ReadOnly = True
.DataSource = ds.Tables("Contractor")
End With
Else
End If
'------------------------------- ตาราง -----------------------------------
'Dim cs As New DataGridViewCellStyle()
'cs.Font = New Font("Ms Sans Serif", 10, FontStyle.Bold)
With DataGridView1
' .ColumnHeadersDefaultCellStyle = cs 'กำหนดข้อความแต่ละคอลัมน์
.Columns(0).HeaderText = "PlannedListId"
.Columns(1).HeaderText = "Item No"
.Columns(2).HeaderText = "Item"
.Columns(3).HeaderText = "ContractorID"
.Columns(4).HeaderText = "CT"
.Columns(5).HeaderText = "Ordertimes"
.Columns(6).HeaderText = "Qty"
.Columns(7).HeaderText = "RecQty"
.Columns(8).HeaderText = "Awaiting Qty"
.Columns(9).HeaderText = "Unit"
.Columns(10).HeaderText = "ModelID"
.Columns(11).HeaderText = "DateCreated"
.Columns(0).Width = 100 'กำหนดความกว้าง
.Columns(1).Width = 210
.Columns(2).Width = 210
.Columns(2).Width = 210
.Columns(3).Width = 75
.Columns(4).Width = 75
.Columns(5).Width = 50
.Columns(6).Width = 50
.Columns(7).Width = 50
.Columns(8).Width = 50
.Columns(9).Width = 50
.Columns(10).Width = 50
.Columns(11).Width = 50
End With
ผมลองใส่ตัวฟังชั่น ISNULLแล้วก็ไม่ได้อ่ะครับ ขึ้นerrorแบบนี้
Wrong number of arguments used with function in query expression 'SUM(ISNULL([IT_TransContractor.ReceivedQty],1)'. พอจะมีวิธีแก้ไหมครับ
ช่วยทีครับT^T
ปล.ขอถามอีกอย่างว่า vb มี ฟังชั่น Nz เหมือนในaccess เปล่าครับ
Tag : .NET, Ms Access, Win (Windows App), VS 2010 (.NET 4.x), Windows, Office