?

Private Sub cmdOK_Click() '“查詢”按鈕
Dim strSQL As String, strTemp1 As String, strTemp2 As String, strTemp3 As String
Dim rs As ADODB.Recordset
' txtBarCode.Text = Trimoney(txtBarCode.Text) '去掉條形碼的空格
If txtBarCode.Text = "" And ChAll.Value = 0 Then '未輸入條形碼，又未選中全部復選框
MsgBox "請輸入需要查詢的商品條形碼，或選擇查詢全部商品。", _
vbInformation + vbOKOnly, "提示"
txtBarCode.SetFocus '設置輸入焦點
Exit Sub '退出過程
End If
strTemp1 = " WHERE DATEDIFF(dd,銷售日期,GETDATE())"
strTemp2 = " AND DATEDIFF(dd,銷售日期,GETDATE())"
Select Case cmbDate.ListIndex
Case 0: strTemp3 = "=0" '今天
Case 1: strTemp3 = "<=7" '本周
Case 2: strTemp3 = "<=30" '本月
Case 3: strTemp3 = "<=365" '本年
Case 4: '全部
strTemp1 = ""
strTemp2 = ""
strTemp3 = ""
End Select
If ChAll.Value = 0 Then '未選中全選
strSQL = "SELECT BarCode FROM Goods WHERE BarCode='" & txtBarCode.Text & "'"
Set rs = QueryExt(strSQL) '查詢指定條碼
If rs.EOF Then
MsgBox "找不到對應的商品，請檢查輸入的條形碼！", _
vbInformation + vbOKOnly, "提示"
txtBarCode.SetFocus '設置輸入焦點
Exit Sub '退出過程
End If
rs.Close
Select Case cmbStyle.ListIndex '查詢類型
Case 0 '明細銷售記錄
strSQL = "SELECT goods.GoodsName AS 商品名稱,goods.BarCode AS 條形碼," & _
"goods.GoodsPrice AS 商品價格,sale.SalePrice AS 銷售價格,sale.SaleNum AS 銷售數量," & _
"sale.SaleDate AS 銷售日期 FROM dbo.sale INNER JOIN goods ON " & _
"sale.GoodsID =goods.GoodsID WHERE 條形碼='" & txtBarCode.Text & " '" & strTemp2 & strTemp3
Case 1 '銷售統計
strSQL = "SELECT goods.GoodsName AS 商品名稱,goods.BarCode AS 條形碼,goods.GoodsPrice AS 商品價格" & _
"AVG(sale.SalePrice) AS 平均銷售價格,SUM(sale.SaleNum) AS 總銷售數量," & _
"SUM(sale.saleprice*sale.salenum) AS 總銷售額 FROM sale INNER JOIN goods ON " & _
"sale.GoodsID =goods.GoodsID WHERE 條形碼='" & txtBarCode.Text & " '" & _
strTemp2 & strTemp3 & "GROUP BY 商品名稱,條形碼,商品價格"
End Select
Set rs = QueryExt(strSQL) '執行SQL語句
Else
strSQL = "SELECT goods.GoodsName AS 商品名稱,goods.BarCode AS 條形碼," & _
"goods.GoodsPrice AS 商品價格,AVG(sale.SalePrice) AS 平均銷售價格,SUM(sale.SaleNum) AS 總銷售數量," & _
"SUM(sale.SaleNum*sale.SalePrice) AS 總銷售額 FROM dbo.sale INNER JOIN goods ON " & _
"sale.GoodsID =goods.GoodsID " & strTemp1 & strTemp3 & " GROUP BY 商品名稱,條形碼,商品價格"
Select Case cmbStyle.ListIndex '查詢類型
Case 0 '明細銷售記錄
strSQL = "SELECT goods.GoodsName AS 名稱,goods.BarCode AS 條形碼," & _
"goods.GoodsPrice AS 商品價格,sale.SalePrice AS 銷售價格,sale.SaleNum AS 銷售數量, " & _
"sale.SaleDate AS 銷售日期 FROM dbo.sale INNER JOIN goods " & _
"ON sale.GoodsID =goods.GoodsID " & strTemp1 & strTemp3 & IIf(chGroup.Value = 0, "", " ORDER BY 商品名稱")
Set rs = QueryExt(strSQL) '執行SQL語句
Case 1 '銷售統計
Set rs = QueryExt(strSQL) '執行SQL語句

Case 2 '排行榜－總銷售量
Set rs = QueryExt(strSQL & " ORDER BY 總銷售量 DESC") '執行SQL語句
Case 3 '排行榜－總銷售額
Set rs = QueryExt(strSQL & " ORDER BY 總銷售額 DESC") '執行SQL語句
End Select
End If
Set Grid1.DataSource = rs '設置表格的數據源
Grid1.MergeCells = chGroup.Value '設置是否合并單元格
ShowSearch True '控制控件顯示
Grid1.Refresh '刷新數據
frmMain.StatusBar1.Panels(1) = "當前查詢共 " & rs.RecordCount & " 條銷售記錄。"
Exit Sub
err1:
MsgBox Err.Description, vbCritical + vbOKOnly, "警告"
End Sub

?

mshflexgrid

轉載于:https://www.cnblogs.com/zsp2/p/10042133.html

總結

以上是生活随笔為你收集整理的这个窗体的制作的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。