【机房】组合查询--学生上机信息统计
【摘要】
机房收费系统中的操作员可以统计学生上机信息,这里用到了组合查询
窗体界面:
字段名、操作符和组合关系的定义:
Private Sub Form_Load()
Dim i, j, k,...
机房收费系统中的操作员可以统计学生上机信息,这里用到了组合查询
窗体界面:
字段名、操作符和组合关系的定义:
Private Sub Form_Load()
Dim i, j, k, o As Integer
For i = 0 To 2
comboField(i).AddItem "卡号"
comboField(i).AddItem "姓名"
comboField(i).AddItem "上机日期"
comboField(i).AddItem "下机日期"
comboField(i).AddItem "消费金额"
comboField(i).AddItem "余额"
Next
For j = 0 To 2
comboOpSign(j).AddItem "="
comboOpSign(j).AddItem ">"
comboOpSign(j).AddItem "<"
comboOpSign(j).AddItem "<>"
Next
For k = 0 To 1
comboCombRela(k).AddItem "与"
comboCombRela(k).AddItem "或"
Next
End Sub
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
点击清空记录
Private Sub cmdClear_Click()
Dim ctr1 As Control
For Each ctr1 In Me.Controls
If TypeOf ctr1 Is TextBox Then '判断是否是文本框
ctr1.Text = ""
End If
Next
For Each ctr1 In Me.Controls
If TypeOf ctr1 Is ComboBox Then '判断是否是文本框
ctr1.Text = ""
End If
Next
MSHFlexGrid1.Rows = 1
End Sub
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
查询
查询的时候需要先判断是否为空
'判断字段名是否为空
If Trim(comboField(0)) = "" Then
MsgBox "请选择字段名", 64, "温馨提示"
comboField(0).SetFocus
Exit Sub
End If
- 1
- 2
- 3
- 4
- 5
- 6
然后当有组合关系的时候,还需要判断下一个查询条件是否为空
Rem;若是选择组合关系
If Trim(comboCombRela(0)) <> "" Then
'判断字段名是否为空
If Trim(comboField(1)) = "" Then
MsgBox "请选择字段名", 64, "温馨提示"
comboField(1).SetFocus
Exit Sub
End If
'判断操作符是否为空
If Trim(comboOpSign(1)) = "" Then
MsgBox "请选择操作符", 64, "温馨提示"
comboOpSign(1).SetFocus
Exit Sub
End If
'判断操作符是否为空
If Trim(txtInquiryCont2) = "" Then
MsgBox "请输入要查询的内容", 64, "温馨提示"
txtInquiryCont2.SetFocus
Exit Sub
End If
End If
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
组合查询的语句有些长,可以分开写,注意空格是否存在。
Rem:连接数据库
txtSQL = "select * from Line_info where"
'与上一行SQL语句组合
txtSQL = txtSQL & " " & Trim(field(comboField(0).Text)) & " " & _
Trim((comboOpSign(0).Text)) & " " & "'" & _
Trim(txtInquiryCont1.Text) & "'"
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
下面判断组合关系存在时,是否有记录
' 第一个组合关系存在
If Trim(comboCombRela(0).Text <> "") Then
txtSQL = txtSQL & " " & field(Trim(comboCombRela(0).Text)) & " " & field(comboField(1).Text) & " " & comboOpSign(1).Text & " " & "'" & Trim(txtInquiryCont2.Text) & "'"
End If
'第二个组合关系存在
If Trim(comboCombRela(1).Text <> "") Then
txtSQL = txtSQL & "" & field(comboCombRela(1).Text) & "" & Trim(field(comboField(2).Text)) & "" & _
Trim(comboOpSign(2).Text) & "" & "'" & _
Trim(txtInquiryCont3.Text) & "'"
End If
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
若没有结果,还需要重新输入,若存在结果,则需要打印到MSHFlexGrid表格中。
Set mrc = ExecuteSQL(txtSQL, MsgText)
If mrc.EOF = True Then
MsgBox "没有查询到结果,请重新输入", 64, "温馨提示"
Exit Sub
Else
With MSHFlexGrid1
.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) = "余额"
Do While Not mrc.EOF
.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)) '余额
mrc.MoveNext
Loop
End With
End If
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
欢迎斧正~
文章来源: blog.csdn.net,作者:张艳伟_Laura,版权归原作者所有,如需转载,请联系作者。
原文链接:blog.csdn.net/Laura__zhang/article/details/107465198
【版权声明】本文为华为云社区用户转载文章,如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱:
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论(0)