在"发票信息表中",现有4000多条记录。我的程序可以按照5个条件单独或组合查询，如下图按“货物名称”和“业务员”两个条件查询，显示有8条记录符合条件。我的愿望是，将这8条记录的“数量”一项进行汇总，并在这个界面中的一个控件中显示出来。由于我的查询结果RS是随查询条件而变的，我想不能用“SUM（数量）FROM 表”中这样办吧，请您指点迷津！
Private Sub fpxxquery_Click()
Dim sql As String
Dim rs As New ADODB.Recordset
Dim bf(5) As Boolean
sql = "select * from fpxxb where "
'判断是否按发票号码进行查询
If checkbox(0).Value Then
If Trim(textfphm.Text) = "" Then
MsgBox "发票号码不能为空!", vbOKOnly + vbExclamation, "警告"
textfphm.SetFocus
Exit Sub
Else
bf(0) = True
'组合查询语句
sql = sql & "fphm='" & Trim(textfphm.Text) & "'"
End If
End If
'判断是否按购货单位进行查询
If checkbox(1) Then
If Trim(cobghdw.Text) = "" Then
MsgBox "购货单位不能为空!", vbOKOnly + vbExclamation, "警告"
cobghdw.SetFocus
Exit Sub
Else
bf(1) = True
If bf(0) Then
'组合查询语句
sql = sql & "and ghdw='" & Trim(cobghdw.Text) & "'"
Else
sql = sql & "ghdw='" & Trim(cobghdw.Text) & "'"
End If
End If
End If
'判断是否选择按货物名称查询
If checkbox(2) Then
If Trim(cobhwmc.Text) = "" Then
MsgBox "货物名称不能为空!", vbOKOnly + vbExclamation, "警告"
cobhwmc.SetFocus
Exit Sub
Else
bf(2) = True
If bf(0) Or bf(1) Then
'组合查询语句
sql = sql & "and hwmc='" & Trim(cobhwmc.Text) & "'"
Else
sql = sql & "hwmc='" & Trim(cobhwmc.Text) & "'"
End If
End If
End If
'判断是否按开票日期进行查询
If checkbox(3) Then '注意：自已的代码！！！！！
bf(3) = True
If bf(0) Or bf(1) Or bf(2) Then
'组合查询语句
sql = sql & "and kprq between '" & Format(dtpkprq1.Value, "YYYY-MM-DD") & "'and '" & Format(dtpkprq2.Value, "YYYY-MM-DD") & "'"
Else
sql = sql & "kprq between '" & Format(dtpkprq1.Value, "YYYY-MM-DD") & "'and '" & Format(dtpkprq2.Value, "YYYY-MM-DD") & "'"
End If
End If
'判断是否按业务员进行查询
If checkbox(4) Then
If Trim(cobywy.Text) = "" Then
MsgBox "请输入业务员!", vbOKOnly + vbExclamation, "警告!"
cobywy.SetFocus
Exit Sub
Else
bf(4) = True
If bf(0) Or bf(1) Or bf(2) Or bf(3) Then
'组合查询语句
sql = sql & "and ywy='" & cobywy.Text & "'"
Else
sql = sql & "ywy='" & cobywy.Text & "'"
End If
End If
End If
'判断是否设置查询方式
If Not (bf(0) Or bf(1) Or bf(2) Or bf(3) Or bf(4)) Then
MsgBox "请设置查询方式!", vbOKOnly + vbExclamation, "警告"
Exit Sub
End If
'查询所有满足条件的内容
sql = sql & " order by bh "
Set rs = transactsql(sql)
If rs.EOF = True Then
MsgBox "没有找到要查询的信息!", vbOKOnly
Else
With fpxxquerygrid
.Rows = 1
Do While Not rs.EOF
.Rows = .Rows + 1
.TextMatrix(.Rows - 1, 0) = rs.Fields(0)
.TextMatrix(.Rows - 1, 1) = rs.Fields(1)
.TextMatrix(.Rows - 1, 2) = rs.Fields(2)
.TextMatrix(.Rows - 1, 3) = rs.Fields(3)
.TextMatrix(.Rows - 1, 4) = rs.Fields(4)
.TextMatrix(.Rows - 1, 5) = rs.Fields(5)
.TextMatrix(.Rows - 1, 6) = rs.Fields(6)
.TextMatrix(.Rows - 1, 7) = rs.Fields(7)
.TextMatrix(.Rows - 1, 8) = rs.Fields(8)
.TextMatrix(.Rows - 1, 9) = rs.Fields(9)
.TextMatrix(.Rows - 1, 10) = rs.Fields(10)
.TextMatrix(.Rows - 1, 11) = rs.Fields(11)
.TextMatrix(.Rows - 1, 12) = rs.Fields(12)
rs.MoveNext
End With
End If
texthz.Text = rs.RecordCount'显示符合条件的记录数
End Sub
