前言：
上一篇是对机房收费系统的简单功能窗体的介绍，这次给大家介绍一下组合查询。刚敲组合查询的时候，别的人就会说好难好难，每每听到这些负能量的话，我就会暗自和自己说，我没有必要在没真正去做某件事之前，就去定义一件事情的难易，带着我必定会轻松解决的心理，结果真的就是轻松解决了。下面呢我就以学生上机统计信息查询为例，向大家揭开组合查询的神秘面纱！
内容：
1、理清思路：
2、代码技巧：
这次敲机房收费系统才明白一个道理，我们的代码不能仅仅是实现一个功能，也要考虑代码的时间和空间复杂度，这里就要提到变量作用。组合查询这点表现得很明显，典型的就是文本框文本与数据库字段的转换，这样可以很大程度减少代码的复杂度，看起来也特别的清晰明了！
Select Case Combo1.Text '文本框文本与数据库字段的转换
Case "卡号"
strcon1 = "cardno"
Case "姓名"
strcon1 = "studentname"
Case "上机日期"
strcon1 = "ondate"
Case "上机时间"
strcon1 = "ontime"
Case "下机日期"
strcon1 = "offdate"
Case "下机时间"
strcon1 = "offtime"
Case "消费金额"
strcon1 = "consume"
Case "余额"
strcon1 = "cash"
Case "备注"
strcon1 = "status"
End Select
3、代码实现：这里需要注意的是只有combo7有文本，第二行的各个框才可用。combo8有文本，第三行的各个框才可用。也就是要用到第二行，第二行的enabled值为true。用到第三行，第三行的enabled的值为true。
Private Sub Command2_Click()
Dim mrc As ADODB.Recordset
Dim txtsql As String
Dim Msgtext As String
Dim strcon1, strcon2, strcon3, strcon4, strcon5
Select Case Combo1.Text '文本框文本与数据库字段的转换
Case "卡号"
strcon1 = "cardno"
Case "姓名"
strcon1 = "studentname"
Case "上机日期"
strcon1 = "ondate"
Case "上机时间"
strcon1 = "ontime"
Case "下机日期"
strcon1 = "offdate"
Case "下机时间"
strcon1 = "offtime"
Case "消费金额"
strcon1 = "consume"
Case "余额"
strcon1 = "cash"
Case "备注"
strcon1 = "status"
End Select
If Not Testtxt(Combo1.Text) Then '判断combo是否为空
MsgBox "请选择字段名", vbOKOnly + vbExclamation, "警告"
Combo1.SetFocus
Else
If Not Testtxt(Combo4.Text) Then '判断combo是否为空
MsgBox "请选择操作符！", vbOKOnly + vbExclamation, "警告"
Combo4.SetFocus
Else
If Not Testtxt(Text1.Text) Then '判断combo是否为空
MsgBox "请输入要查询的内容", vbOKOnly + vbExclamation, "警告"
Text1.SetFocus
Else
'第一行条件
txtsql = "select * from Line_Info where " & strcon1 & Combo4.Text & "'" & Trim(Text1.Text) & "'"
Set mrc = ExecuteSQL(txtsql, Msgtext)
If mrc.EOF = True Then '判断是否有对应条件的记录
MsgBox "此条件没有上机记录！", vbOKOnly + vbExclamation, "警告"
Combo1.Text = ""
Combo4.Text = ""
Text1.Text = ""
MSFlexGrid1.Clear
With MSFlexGrid1 '清除后显示表头
.CellAlignment = 4
.Rows = 1
.TextMatrix(0, 0) = "卡号"
.TextMatrix(0, 1) = "姓名"
.TextMatrix(0, 2) = "上机日期"
.TextMatrix(0, 3) = "上机时间"
.TextMatrix(0, 4) = "下机日期"
.TextMatrix(0, 5) = "下机时间"
.TextMatrix(0, 6) = "消费金额"
.TextMatrix(0, 7) = "余额"
.TextMatrix(0, 8) = "备注"
End With
Exit Sub
End If
End If
End If
If Not Testtxt(Combo7.Text) Then '如果combo7没有文本则直接跳到case1
GoTo case1
Else '如果有则判断第二行的条件
If Not Testtxt(Combo2.Text) Then '判断combo框是否为空
MsgBox "请选择字段名", vbOKOnly + vbExclamation, "警告"
Combo2.SetFocus
Else
If Not Testtxt(Combo5.Text) Then
MsgBox "请选择操作符！", vbOKOnly + vbExclamation, "警告"
Combo5.SetFocus
Else
If Not Testtxt(Text2.Text) Then
MsgBox "请输入要查询的内容", vbOKOnly + vbExclamation, "警告"
Text2.SetFocus
Else
Select Case Combo7.Text
Case "或"
strcon2 = "or "
Case "与"
strcon2 = "and "
End Select
Select Case Combo2.Text '文本框文本与数据库字段的转换
Case "卡号"
strcon3 = "cardno"
Case "姓名"
strcon3 = "studentname"
Case "上机日期"
strcon3 = "ondate"
Case "上机时间"
strcon3 = "ontime"
Case "下机日期"
strcon3 = "offdate"
Case "下机时间"
strcon3 = "offtime"
Case "消费金额"
strcon3 = "consume"
Case "余额"
strcon3 = "cash"
Case "备注"
strcon3 = "status"
End Select
txtsql = txtsql & strcon2 & " " & strcon3 & Combo5.Text & "'" & Trim(Text2.Text) & "'"
Set mrc = ExecuteSQL(txtsql, Msgtext)
If mrc.EOF = True Then
MsgBox "没有该条件下的上机记录", vbOKOnly + vbExclamation, "警告"
Exit Sub
Combo1.Text = ""
Combo4.Text = ""
Text1.Text = ""
Combo2.Text = ""
Combo5.Text = ""
Text2.Text = ""
Combo7.Text = ""
With MSFlexGrid1 '清除后显示表头
.CellAlignment = 4
.Rows = 1
.TextMatrix(0, 0) = "卡号"
.TextMatrix(0, 1) = "姓名"
.TextMatrix(0, 2) = "上机日期"
.TextMatrix(0, 3) = "上机时间"
.TextMatrix(0, 4) = "下机日期"
.TextMatrix(0, 5) = "下机时间"
.TextMatrix(0, 6) = "消费金额"
.TextMatrix(0, 7) = "余额"
.TextMatrix(0, 8) = "备注"
End With
End If
End If
End If
End If
End If
If Combo8.Text = "" Then '如果combo8没有文本则直接跳到case1
GoTo case1
Else
If Not Testtxt(Combo3.Text) Then
MsgBox "请选择字段名", vbOKOnly + vbExclamation, "警告"
Combo2.SetFocus
Else
If Not Testtxt(Combo6.Text) Then
MsgBox "请选择操作符！", vbOKOnly + vbExclamation, "警告"
Combo5.SetFocus
Else
If Not Testtxt(Text3.Text) Then
MsgBox "请输入要查询的内容", vbOKOnly + vbExclamation, "警告"
Text2.SetFocus
Else
Select Case Combo8.Text
Case "或"
strcon4 = "or "
Case "与"
strcon4 = "and "
End Select
Select Case Combo3.Text
Case "卡号"
strcon5 = "cardno"
Case "姓名"
strcon5 = "studentname"
Case "上机日期"
strcon5 = "ondate"
Case "上机时间"
strcon5 = "ontime"
Case "下机日期"
strcon5 = "offdate"
Case "下机时间"
strcon5 = "offtime"
Case "消费金额"
strcon5 = "consume"
Case "余额"
strcon5 = "cash"
Case "备注"
strcon5 = "status"
End Select
txtsql = txtsql & " " & strcon4 & strcon5 & Combo6.Text & "'" & Trim(Text3.Text) & "'"
Set mrc = ExecuteSQL(txtsql, Msgtext)
If mrc.EOF = True Then
MsgBox "没有该条件下的上机记录！", vbOKOnly + vbExclamation, "警告"
Combo1.Text = ""
Combo4.Text = ""
Text1.Text = ""
Combo2.Text = ""
Combo5.Text = ""
Text2.Text = ""
Combo7.Text = ""
Combo3.Text = ""
Combo6.Text = ""
Combo8.Text = ""
Text3.Text = ""
With MSFlexGrid1 '清除后显示表头
.CellAlignment = 4
.Rows = 1
.TextMatrix(0, 0) = "卡号"
.TextMatrix(0, 1) = "姓名"
.TextMatrix(0, 2) = "上机日期"
.TextMatrix(0, 3) = "上机时间"
.TextMatrix(0, 4) = "下机日期"
.TextMatrix(0, 5) = "下机时间"
.TextMatrix(0, 6) = "消费金额"
.TextMatrix(0, 7) = "余额"
.TextMatrix(0, 8) = "备注"
End With
Exit Sub
End If
End If
End If
End If
End If
case1:
With MSFlexGrid1 '窗体加载时显示表头
.CellAlignment = 4
.Rows = 1
.TextMatrix(0, 0) = "卡号"
.TextMatrix(0, 1) = "姓名"
.TextMatrix(0, 2) = "上机日期"
.TextMatrix(0, 3) = "上机时间"
.TextMatrix(0, 4) = "下机日期"
.TextMatrix(0, 5) = "下机时间"
.TextMatrix(0, 6) = "消费金额"
.TextMatrix(0, 7) = "余额"
.TextMatrix(0, 8) = "备注"
Do While Not mrc.EOF 'MSFlexGrid1显示数据
.Rows = .Rows + 1
.CellAlignment = 4
.TextMatrix(.Rows - 1, 0) = Trim(mrc.Fields(1) & "")
.TextMatrix(.Rows - 1, 1) = Trim(mrc.Fields(3) & "")
.TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(6) & "")
.TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(7) & "")
.TextMatrix(.Rows - 1, 4) = Trim(mrc.Fields(8) & "")
.TextMatrix(.Rows - 1, 5) = Trim(mrc.Fields(9) & "")
.TextMatrix(.Rows - 1, 6) = Format(mrc.Fields(10), "0.00") & ""
.TextMatrix(.Rows - 1, 7) = Format(mrc.Fields(12), "0.00") & ""
.TextMatrix(.Rows - 1, 8) = Trim(mrc.Fields(13) & "")
mrc.MoveNext
Loop
End With
End If
End Sub
总结：
组合查询其实没有大家想象中的那么难，很多代码都是重复的，还是那句话，只要我们理清思路就OK了！
感谢您的阅读！