组合查询其实就是多个查询条件的限定,只要思路清晰了,执行起来便容易多了。首先我把这三个查询条件看作三个整体,定义了三个过程,并将combo的字段转换成数据库中对应的字段名,执行语句时直接调用,这样在查询过程中就减少了代码量。
比如定义第一查询条件:
Private Sub InquireCondition1()
'定义第一查询条件过程,将combo的字段名转换成数据库对应的字段名
Select Case Combo1
Case "卡号"
Ziduan(0) = "cardNo"
Case "学号"
Ziduan(0) = "studentNo"
Case "姓名"
Ziduan(0) = "studentName"
Case "性别"
Ziduan(0) = "sex"
Case "系别"
Ziduan(0) = "Department"
Case "年级"
Ziduan(0) = "grade"
Case "班号"
Ziduan(0) = "class"
End Select
Select Case Combo7
Case "与"
ZuheRelation(0) = "and"
Case "或"
ZuheRelation(0) = "or"
End Select
End Sub
然后在查询中编写代码如下:
Dim msgText As String
'将sql语句定义为一个过程,在查询事件中直接引用
txtSQL = "select * from Line_Info where "
If Combo1.Text = "" Then '判断字段名是否为空
MsgBox "请选择字段名!", vbOKOnly + vbExclamation, "警告"
Combo1.SetFocus
Exit Sub
End If
If Combo4.Text = "" Then '判断选择符是否为空
MsgBox "请选择操作符!", vbOKOnly + vbExclamation, "警告"
Combo4.SetFocus
Exit Sub
End If
If Text1.Text = "" Then '判断查询内容是否为空
MsgBox "请输入要查询的内容!", vbOKOnly + vbExclamation, "警告"
Text1.SetFocus
Exit Sub
End If
Call InquireCondition1
If Combo7.Text = "" Then '执行第一查询条件
txtSQL = txtSQL & " " & Trim(Ziduan(0)) & "" & Trim(Combo4.Text) & "'" & Trim(Text1.Text) & "'"
Else '执行第一查询条件和第二查询条件
If Combo2.Text = "" Then
MsgBox "请选择字段名!", vbOKOnly + vbExclamation, "警告"
Combo2.SetFocus
Exit Sub
End If
If Combo5.Text = "" Then
MsgBox "请选择操作符!", vbOKOnly + vbExclamation, "警告"
Combo5.SetFocus
Exit Sub
End If
If Text2.Text = "" Then
MsgBox "请输入要查询的内容!", vbOKOnly + vbExclamation, "警告"
Text2.SetFocus
Exit Sub
End If
Call InquireCondition2
If Combo8.Text = "" Then
txtSQL = txtSQL & "" & Trim(Ziduan(0)) & "" & Trim(Combo4.Text) & "'" & Trim(Text1.Text) & "'" & " " & Trim(ZuheRelation(0)) & " " & Trim(Ziduan(1)) & " " & Trim(Combo5.Text) & "'" & Trim(Text2.Text) & "'"
Else '三个条件同时执行
If Combo3.Text = "" Then
MsgBox "请选择字段名!", vbOKOnly + vbExclamation, "警告"
Combo3.SetFocus
Exit Sub
End If
If Combo6.Text = "" Then
MsgBox "请选择操作符!", vbOKOnly + vbExclamation, "警告"
Combo6.SetFocus
Exit Sub
End If
If Text3.Text = "" Then
MsgBox "请输入要查询的内容!", vbOKOnly + vbExclamation, "警告"
Text3.SetFocus
Exit Sub
End If
Call InquireCondition3
txtSQL = txtSQL & "" & Trim(Ziduan(0)) & "" & Trim(Combo4.Text) & "'" & Trim(Text1.Text) & "'" & "" & Trim(ZuheRelation(0)) & " " & Trim(Ziduan(1)) & "" & Trim(Combo5.Text) & "'" & Trim(Text2.Text) & "'" & "" & Trim(ZuheRelation(1)) & " " & Trim(Ziduan(2)) & "" & Trim(Combo6.Text) & "'" & Trim(Text3.Text) & "'"
End If
End If
这样便可以顺利实现了。