机房收费系统中一个比较难理解的点-组合查询,组合查询窗体首先要理解逻辑关系,有很多种操作步骤及理解方法。理解此窗体的功能:通过选择字段名中的选择项及操作符来查询相应表格中的内容,并且可以利用组合查询的方式得到多个查询条件下的结果。并将结果导出为Excel形式。
以学生上机统计信息为例:
窗体逻辑顺序(操作步骤):
1.首先在字段名中有时间查询,在要查询的内容中设置一个关于时间选择的控件
2.字段名中有姓名等项目,在操作符中不能选择"<"或者">"
3.如果选择第一行则可以进行查询
4.如果选择第一个组合关系,判断前两行
5.如果选择第二个组合关系,判断所有
代码实现:
1.字段名
Function Field(i As String) As String
Select Case i
Case "卡号"
Field = "cardno"
Case "姓名"
Field = "studentname"
Case "上机日期"
Field = "ondate"
Case "上机时间"
Field = "ontime"
Case "下机日期"
Field = "offdate"
Case "下机时间 "
Field = "offtime"
Case "消费金额"
Field = "concume"
Case "余额"
Field = "cash"
Case "备注"
Field = "status"
Case "或"
Field = "or"
Case "与"
fiels = "and"
End Select
End Function
2.查询
'检查条件输入
If Trim(Comfield1.Text) = "" Or Trim(Comperator1.Text) = "" Or Trim(Text1.Text) = "" Then
MsgBox "请输入完整的查询条件", , "提示"
Exit Sub
End If
'查询line_info表
txtsql = "select * from line_info where"
txtsql = txtsql & " " & Field(Comfield1.Text) & " " & Comperator1.Text & "'" & Trim(Text1.Text) & "'"
'判断第一个组合关系是否选中
If Trim(Comrelation1.Text <> "") Then
'判断第二行内容是否填写完整,且符合要求
If Trim(Comfield2.Text) = "" Or Trim(Comperator2.Text) = "" Or Trim(Text2.Text) = "" Then
MsgBox "您选择了第一个组合关系,请输入第二行条件查询!", vbOKOnly + vbExclamation, "提示"
Exit Sub
Else
txtsql = txtsql & " " & Field(Comrelation1.Text) & " " & Field(Comfield2.Text) & " " & Comperator2.Text & "'" & Trim(Text2.Text) & "'"
End If
End If
'将前两行的条件联系起来,完成查询
If Trim(Comrelation2.Text) <> "" Then
'判断第二个组合关系是否选中
If Trim(Comfield3.Text) = "" Or Trim(Comperator3.Text) = "" Or Trim(Text3.Text) = "" Then
'如果选中,判断第二行选项内容是否填写完整
MsgBox "您选择了第二个组合关系,请输入第三行条件查询!", vbOKOnly + vbExclamation, "提示"
Exit Sub
Else
txtsql = txtsql & " " & Field(Comrelation2.Text) & " " & Field(Comfield3.Text) & " " & Trim(Comperator3.Text) & "'" & Trim(Text3.Text) & "'"
'将三行信息联系起来
End If
End If
Set mrc = ExecuteSQL(txtsql, msgtext)
If mrc.EOF = True Then '检查信息是否存在,如果不存在给出提示并清空所有文本框
MsgBox "没有查询到结果,可能会你输入的信息不存在,或者信息矛盾", vbOKOnly + vbExclamation, "警告"
Comfield1.Text = ""
Comfield2.Text = ""
Comfield3.Text = ""
Comperator1.Text = ""
Comperator2.Text = ""
Comperator3.Text = ""
Text1.Text = ""
Text2.Text = ""
Text3.Text = ""
Comrelation1.Text = ""
Comrelation2.Text = ""
Exit Sub
Else
With MSFlexGrid1
.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
.Rows = .Rows + 1
.TextMatrix(.Rows - 1, 0) = Trim(mrc!cardno)
.TextMatrix(.Rows - 1, 1) = mrc!studentname
.TextMatrix(.Rows - 1, 2) = mrc!ondate
.TextMatrix(.Rows - 1, 3) = mrc!OnTime
.TextMatrix(.Rows - 1, 4) = mrc!offdate
.TextMatrix(.Rows - 1, 5) = mrc!offtime
.TextMatrix(.Rows - 1, 6) = mrc!consume
.TextMatrix(.Rows - 1, 7) = mrc!cash
.TextMatrix(.Rows - 1, 8) = mrc!Status
mrc.MoveNext
Loop
mrc.Close
End With
End If
End Sub
3.时间控件及操作符
Private Sub DTP1_Change()
'如果选择上下机日期
If Trim(Comfield1.Text) = "上机日期" Or Trim(Comfield1.Text) = "下机日期" Then
Text1.Text = DTP1.Value
Else
If Trim(Comfield1.Text) = "上机时间" Or Trim(Comfield1.Text) = "下机时间" Then
'将dtpicker控件改为time格式,并可见
DTP1.Format = 2 '默认为0,1为日期格式
DTP1.Value = Time
Text1.Text = DTP1.Value
End If
End If
End Sub
Private Sub Comfield1_Click()
'如果选择上下机日期
If Trim(Comfield1.Text) = "上机日期" Or Trim(Comfield1.Text) = "下机日期" Or Trim(Comfield1.Text) = "上机时间" Or Trim(Comfield1.Text) = "下机时间" Then
DTP1.Visible = True
Else
DTP1.Visible = False
End If
'如果选择了姓名,则操作符为=或<>
If Trim(Comfield1.Text) = "姓名" Then
'首先清空comperator控件
Comperator1.Clear
Comperator1.AddItem "="
Comperator1.AddItem "<>"
Else
'清空comperator控件
Comperator1.Clear
Comperator1.AddItem "="
Comperator1.AddItem "<"
Comperator1.AddItem ">"
Comperator1.AddItem "<>"
End If
End Sub