查询思路:
1.先判断组合关系,如果不使用组合关系,则查询单条内容,如果使用第一个组合关系,则查询前两条内容,如果使用第二个组合关系,则查询后两条内容,如果两个组合关系都使用,则查询三条内容。
2.查询语句是一条SQL语句,所以字段名,操作符和组合关系下拉框中的内容要和数据表中的字段或SQL语言相对应,可以在模块中定义一个转换函数field。
3.组合查询,使用循环语句将查询记录显示在表格上。
重要代码:
模块中定义转换函数:
Public Function field(a As String) As String
'选中的内容统一名字
Select Case a
Case "卡号"
field = "cardno"
Case "学号"
field = "studentno"
Case "姓名"
field = "studentname"
Case "性别"
field = "sex"
Case "系别"
field = "department"
Case "年级"
field = "grade"
Case "班级"
field = "class"
Case "与"
field = "and "
Case "或"
field = "or "
Case "="
field = "="
Case "<"
field = "<"
Case ">"
field = ">"
Case "<>"
field = "<>"
Case "上机日期"
field = "ondate"
Case "上机时间"
field = "ontime"
Case "下机日期"
field = "offdate"
Case "下机时间"
field = "offtime"
Case "消费金额"
field = "consume"
Case "余额"
field = "cash"
Case "教师"
field = "UserID"
Case "注册日期"
field = "LoginDate"
Case "注册时间"
field = "LoginTime"
Case "注销日期"
field = "LogoutDate"
Case "注销时间"
field = "LogoutTime"
Case "机器名"
field = "computer"
End Select
End Function
根据字段名中选择不同的字段使用不同控件:
Private Sub Combo1_Click(Index As Integer)
'提示框内容先清空
Label1.Caption = ""
'循环数组
Dim i As Integer
i = Index '利用索引判断使用的控件
Text1(i).Text = ""
'如果字段名是时间日期,则使用时间日期控件
For i = 0 To 2
If Combo1(i).Text = "上机日期" Or Combo1(i).Text = "上机时间" Or Combo1(i).Text = "下机日期" Or Combo1(i).Text = "下机时间" Then
Label1.Caption = "日期查询格式为“yyyy/mm/dd”,时间查询格式为”hh:mm:ss“"
End If
If Combo1(i).Text = "上机日期" Or Combo1(i).Text = "下机日期" Then
DTPicker1(i).Format = dtpLongDate
DTPicker1(i).Visible = True
Text1(i).Visible = False
Else
If Combo1(i).Text = "上机时间" Or Combo1(i).Text = "下机时间" Then
DTPicker1(i).Format = dtpTime '定义日期格式
DTPicker1(i).Visible = True
Text1(i).Visible = False
Else
DTPicker1(i).Visible = False
Text1(i).Visible = True
End If
End If
'定义文本框字符串数量
If Combo1(i).Text = "卡号" Or Combo1(i).Text = "姓名" Then
Text1(i).MaxLength = 10
End If
If Combo1(i).Text = "消费金额" Or Combo1(i).Text = "余额" Then
Text1(i).MaxLength = 8
End If
Next i
End Sub
查询代码:
Private Sub Command1_Click()
Dim mrc As ADODB.Recordset
Dim txtsql As String
Dim msgtext As String
MSFlexGrid1.rows = 2
MSFlexGrid1.Clear
Label1.Caption = ""
'把日历控件的值赋给文本框
Dim i As Integer
For i = 0 To 2
If DTPicker1(i).Visible = True Then
Text1(i).Text = DTPicker1(i).Value
End If
Next i
'组合查询
txtsql = "select * from line_info where "
'判断是否为空
'不使用组合关系
If Combo3(0).Text = "" And Combo3(1).Text = "" Then '判断是否组合查询
If Combo1(0).Text = "" And Combo1(1).Text = "" And Combo1(2).Text = "" Then '判断字段是否输入
Label1.Caption = "请输入字段名"
Exit Sub
Else
If Combo1(0).Text <> "" Then '如果第一行输入
Combo1(1).Text = "" '其他两行清空
Combo1(2).Text = ""
'判断是否为空
If Combo2(0).Text = "" Or Text1(0).Text = "" Then
Label1.Caption = "请将第一行内容填写完整!"
Exit Sub
Else
'判断数据类型
If Combo1(0).Text = "消费金额" Or Combo1(0).Text = "余额" Then
If Not IsNumeric(Text1(0).Text) Then
Label1.Caption = "请输入数字!"
Text1(0).Text = ""
Text1(0).SetFocus
Exit Sub
End If
End If
'组合查询
txtsql = txtsql & field(Combo1(0).Text) & " " & field(Trim(Combo2(0).Text)) & "'" & Trim(Text1(0).Text) & "'"
End If
End If
If Combo1(1).Text <> "" Then '如果第二行输入
Combo1(0).Text = "" '其他两行清空
Combo1(2).Text = ""
'判断是否为空
If Combo2(1).Text = "" And Text1(1).Text = "" Then
Label1.Caption = "请将第二行内容填写完整!"
Exit Sub
Else
'判断数据类型
If Combo1(1).Text = "消费金额" Or Combo1(1).Text = "余额" Then
If Not IsNumeric(Text1(1).Text) Then
Label1.Caption = "请输入数字!"
Text1(1).Text = ""
Text1(1).SetFocus
Exit Sub
End If
End If
'组合查询
txtsql = txtsql & field(Combo1(1).Text) & " " & field(Trim(Combo2(1).Text)) & "'" & Trim(Text1(1).Text) & "'"
End If
End If
If Combo1(2).Text <> "" Then '如果第三行输入
Combo1(1).Text = "" '其他两行清空
Combo1(0).Text = ""
If Combo2(2).Text = "" And Text1(2).Text = "" Then
Label1.Caption = "请将第三行内容填写完整!"
Exit Sub
Else
'判断数据类型
If Combo1(2).Text = "消费金额" Or Combo1(2).Text = "余额" Then
If Not IsNumeric(Text1(2).Text) Then
Label1.Caption = "请输入数字!"
Text1(2).Text = ""
Text1(2).SetFocus
Exit Sub
End If
End If
'组合查询
txtsql = txtsql & field(Combo1(2).Text) & " " & field(Trim(Combo2(2).Text)) & "'" & Trim(Text1(2).Text) & "'"
End If
End If
'表格信息录入
With MSFlexGrid1 '单元格居中对齐
.rows = 1
.CellAlignment = 4
.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
Set mrc = ExecuteSQL(txtsql, msgtext)
If mrc.EOF = True Then
Label1.Caption = "查询不到记录,请重新输入!"
Exit Sub
Else
Do While mrc.EOF = False
With 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) = Trim(mrc.Fields(11)) & ""
.TextMatrix(.rows - 1, 7) = Trim(mrc.Fields(12)) & ""
.TextMatrix(.rows - 1, 8) = Trim(mrc.Fields(13))
End With
mrc.MoveNext
Loop
End If
End If
End If
'前两个组合查询
If Combo3(0).Text <> "" And Combo3(1).Text = "" Then
'判断是否为空
If Combo1(0).Text = "" Or Combo1(1).Text = "" Or Combo2(0).Text = "" Or Combo2(1).Text = "" Or Text1(0).Text = "" Or Text1(1).Text = "" Then
Label1.Caption = "请将前两行内容填写完整"
Exit Sub
Else
'判断数据类型
If Combo1(0).Text = "消费金额" Or Combo1(0).Text = "余额" Then
If Not IsNumeric(Text1(0).Text) Then
Label1.Caption = "请输入数字!"
Text1(0).Text = ""
Text1(0).SetFocus
Exit Sub
End If
End If
If Combo1(1).Text = "消费金额" Or Combo1(1).Text = "余额" Then
If Not IsNumeric(Text1(1).Text) Then
Label1.Caption = "请输入数字!"
Text1(1).Text = ""
Text1(1).SetFocus
Exit Sub
End If
End If
'组合查询
txtsql = txtsql & field(Combo1(0).Text) & "" & field(Trim(Combo2(0).Text)) & "'" & Trim(Text1(0).Text) & "'"
txtsql = txtsql & field(Combo3(0).Text) & field(Combo1(1).Text) & field(Trim(Combo2(1).Text)) & "'" & Trim(Text1(1).Text) & "'"
Set mrc = ExecuteSQL(txtsql, msgtext)
'表格信息录入
With MSFlexGrid1
.rows = 1
.CellAlignment = 4
.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
If mrc.EOF = True Then
Label1.Caption = "查询不到记录,请重新输入!"
Exit Sub
Else
Do While mrc.EOF = False
With 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) = Trim(mrc.Fields(11)) & ""
.TextMatrix(.rows - 1, 7) = Trim(mrc.Fields(12)) & ""
.TextMatrix(.rows - 1, 8) = Trim(mrc.Fields(13))
End With
mrc.MoveNext
Loop
End If
End If
End If
'后两个组合查询
If Combo3(1).Text <> "" And Combo3(0).Text = "" Then
'判断是否为空
If Combo1(1).Text = "" Or Combo1(2).Text = "" Or Combo2(2).Text = "" Or Combo2(1).Text = "" Or Text1(2).Text = "" Or Text1(1).Text = "" Then
Label1.Caption = "请将后两行内容填写完整"
Exit Sub
Else
'判断数据类型
If Combo1(1).Text = "消费金额" Or Combo1(1).Text = "余额" Then
If Not IsNumeric(Text1(1).Text) Then
Label1.Caption = "请输入数字!"
Text1(1).Text = ""
Text1(1).SetFocus
Exit Sub
End If
End If
If Combo1(2).Text = "消费金额" Or Combo1(2).Text = "余额" Then
If Not IsNumeric(Text1(2).Text) Then
Label1.Caption = "请输入数字!"
Text1(2).Text = ""
Text1(2).SetFocus
Exit Sub
End If
End If
'组合查询
txtsql = txtsql & field(Combo1(1).Text) & "" & field(Trim(Combo2(1).Text)) & "'" & Trim(Text1(1).Text) & "'"
txtsql = txtsql & field(Combo3(1).Text) & field(Combo1(2).Text) & field(Trim(Combo2(2).Text)) & "'" & Trim(Text1(2).Text) & "'"
Set mrc = ExecuteSQL(txtsql, msgtext)
'表格信息录入
With MSFlexGrid1
.rows = 1
.CellAlignment = 4
.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
If mrc.EOF = True Then
Label1.Caption = "查询不到记录,请重新输入!"
Exit Sub
Else
Do While mrc.EOF = False
With 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) = Trim(mrc.Fields(11))
.TextMatrix(.rows - 1, 7) = Trim(mrc.Fields(12))
.TextMatrix(.rows - 1, 8) = Trim(mrc.Fields(13))
End With
mrc.MoveNext
Loop
End If
End If
End If
'三个组合查询
If Combo3(1).Text <> "" And Combo3(0).Text <> "" Then
'判空
If Combo1(0).Text = "" Or Combo2(0).Text = "" Or Text1(0).Text = "" Or Combo1(1).Text = "" _
Or Combo2(1).Text = "" Or Text1(1).Text = "" Or Combo1(2).Text = "" _
Or Combo2(2).Text = "" Or Text1(2).Text = "" Then
Label1.Caption = "请输入相关内容"
Exit Sub
Else
'数据类型
If Combo1(0).Text = "消费金额" Or Combo1(0).Text = "余额" Then
If Not IsNumeric(Text1(0).Text) Then
Label1.Caption = "请输入数字!"
Text1(0).Text = ""
Text1(0).SetFocus
Exit Sub
End If
End If
If Combo1(1).Text = "消费金额" Or Combo1(1).Text = "余额" Then
If Not IsNumeric(Text1(1).Text) Then
Label1.Caption = "请输入数字!"
Text1(1).Text = ""
Text1(1).SetFocus
Exit Sub
End If
End If
If Combo1(2).Text = "消费金额" Or Combo1(2).Text = "余额" Then
If Not IsNumeric(Text1(2).Text) Then
Label1.Caption = "请输入数字!"
Text1(2).Text = ""
Text1(2).SetFocus
Exit Sub
End If
End If
'组合查询
txtsql = txtsql & field(Combo1(0).Text) & " " & field(Trim(Combo2(0).Text)) & "'" & Trim(Text1(0).Text) & "'"
txtsql = txtsql & field(Combo3(0).Text) & field(Combo1(1).Text) & _
field(Trim(Combo2(1).Text)) & "'" & Trim(Text1(1).Text) & "'"
txtsql = txtsql & field(Combo3(1).Text) & field(Combo1(2).Text) & _
field(Trim(Combo2(2).Text)) & "'" & Trim(Text1(2).Text) & "'"
Set mrc = ExecuteSQL(txtsql, msgtext)
'表格信息录入
With MSFlexGrid1
.rows = 1
.CellAlignment = 4
.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
If mrc.EOF = True Then
Label1.Caption = "无记录"
Exit Sub
Else
Do While mrc.EOF = False
With 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) = Trim(mrc.Fields(11)) & ""
.TextMatrix(.rows - 1, 7) = Trim(mrc.Fields(12))
.TextMatrix(.rows - 1, 8) = Trim(mrc.Fields(13))
End With
mrc.MoveNext
Loop
End If
End If
End If
End Sub