VB—联合查询（查询学生信息实例）

       刚刚进行完了机房收费系统关于联合查询的部分，整个代码进行完了，才发现，联合查询的东西不是很多，要用到知识就只有一个写查询的语句，最难的地方就是分析查询情况，首先呢，简单介绍下我整个代码的思路，先列出查询时可能出现的情况：

      1.我们现在将第一句话当成一个查询的条件（卡号=1），仅用这一个条件进行查询

      2.将第一句话和第二句话联合起来，作为一个查询条件，进行查询（卡号=1与学号=1）

      3.将第一、二、三种加起来，视为一种查询的条件，进行查询

     分析完查询可能出现的几种情况，接下来，我们就开始写代码了！

Private Subcmdinquire_Click()       Dim mrc As ADODB.Recordset    Dim txtsql As String    Dim msgtext As String    '定义变量，将文本框中所输入的内容转化成字段输出来    Dim FLD(0 To 4) As String    Dim relation(0 To 1) As String    '定义三种方式，判断文本框中输入的内容是否为空（格式：text1.text<>"")    Dim ok1 As Boolean    Dim ok2 As Boolean    Dim ok3 As Boolean     If cmbfield(0) <> "" Andcmbsign(0) <> "" And Trim(txtinquire(0).Text) <>"" Then        ok1 = True    Else        ok1 = False    End If     If cmbfield(0) <> "" Andcmbsign(0) <> "" And Trim(txtinquire(0).Text) <>"" And cmbandor(0).Text <> "" And cmbfield(1).Text<> "" And cmbsign(1).Text <> "" AndTrim(txtinquire(1).Text) <> "" Then        ok2 = True    Else        ok2 = False    End If     If cmbfield(0) <> "" Andcmbsign(0) <> "" And Trim(txtinquire(0).Text) <>"" And cmbandor(0).Text <> "" And cmbfield(1).Text<> "" And cmbsign(1).Text <> "" AndTrim(txtinquire(1).Text) <> "" And cmbfield(2).Text <>"" And cmbsign(2).Text <> "" AndTrim(txtinquire(2).Text) <> "" Then        ok3 = True    Else        ok3 = False    End If定义一个变量FLD（注意：这里定义变量时所用的名称不能和ＶＢ里的关键字相重复）　　将下拉菜单中所选择的东西转换成数据库中的字段，进行查询    FLD(0) = "work_date"    FLD(1) = "work_time"    FLD(2) = "work_teacher"    FLD(3) = "teacher_type"    FLD(4) = "PC_name"      relation(0) = " and "    relation(1) = " or "    '当三种查询的条件都满足的时候    If ok1 = True Then        If ok2 = True Then            If ok3 = True Then　　　　　　　　　　　　　　　　（注意：ListIndex属性的使用，这里使用listIndex是为了设定下拉菜单中返回值为字符串）                txtsql = "select * fromwork_teacher where " & Trim(FLD(cmbfield(0).ListIndex)) & Trim(cmbsign(0).Text) & "'" &Trim(txtinquire(0).Text) & "'" &relation(cmbandor(0).ListIndex) & Trim(FLD(cmbfield(1).ListIndex)) &Trim(cmbsign(1).Text) & "'" & Trim(txtinquire(1).Text) &"'" & relation(cmbandor(0).ListIndex) &Trim(FLD(cmbfield(2).ListIndex)) & Trim(cmbsign(2).Text) &"'" & Trim(txtinquire(2).Text) & "'"      写查询语句的时候更要注意符号的使用，尤其是单引号和双引号，在这里教给大家一个小方法，我们写完一句查询语句，然后可以 msgbox txtsql 设置一个立即窗口检查下自己是否书写正确！            Else '满足两种查询条件                 txtsql = "select * fromwork_teacher where " & Trim(FLD(cmbfield(0).ListIndex)) &Trim(cmbsign(0).Text) & "'" & Trim(txtinquire(0).Text) &"'" & relation(cmbandor(0).ListIndex) & Trim(FLD(cmbfield(1).ListIndex))& Trim(cmbsign(1).Text) & "'" & Trim(txtinquire(1).Text)& "'"            End If        Else '满足一种查询条件            txtsql = "select * fromwork_teacher where " & Trim(FLD(cmbfield(0).ListIndex)) &Trim(cmbsign(0).Text) & "'" & Trim(txtinquire(0).Text) &"'"         End If    Else  '没有输入完整的条件，查空        Call checktext(0)        Exit Sub    End If     Set mrc = ExecuteSQL(txtsql, msgtext)     If mrc.EOF Then         MsgBox "没有找到符合要求的结果！", vbOKOnly +vbInformation, "提示"         With myflexgrid            .Rows = 1            .CellAlignment = 4            .TextMatrix(0, 0) = "上机日期"            .TextMatrix(0, 1) = "上机时间"            .TextMatrix(0, 2) = "操作教师"            .TextMatrix(0, 3) = "教师类型"            .TextMatrix(0, 4) = "机器号"         End With        Exit Sub    Else        With myflexgrid            .Rows = 1            .CellAlignment = 4            .TextMatrix(0, 0) = "上机日期"            .TextMatrix(0, 1) = "上机时间"            .TextMatrix(0, 2) = "操作教师"            .TextMatrix(0, 3) = "教师类型"            .TextMatrix(0, 4) = "机器号"            Do While Not mrc.EOF                .Rows = .Rows + 1                .TextMatrix(.Rows - 1, 0) =mrc.Fields("work_date")                .TextMatrix(.Rows - 1, 1) =mrc.Fields("work_time")                .TextMatrix(.Rows - 1, 2) =mrc.Fields("work_teacher")                .TextMatrix(.Rows - 1, 3) =mrc.Fields("teacher_type")                .TextMatrix(.Rows - 1, 4) =mrc.Fields("PC_name")                mrc.MoveNext            Loop        End With        mrc.Close     End If End Sub Private SubForm_Load()cmbandor(0).AddItem"与"cmbandor(0).AddItem"或" cmbandor(1).AddItem"与"cmbandor(1).AddItem"或" cmbfield(0).AddItem"上机日期"cmbfield(0).AddItem"上机时间"cmbfield(0).AddItem"操作教师"cmbfield(0).AddItem"教师类型"cmbfield(0).AddItem"机器号"  cmbfield(1).AddItem"上机日期"cmbfield(1).AddItem"上机时间"cmbfield(1).AddItem"操作教师"cmbfield(1).AddItem"教师类型"cmbfield(1).AddItem"机器号" cmbfield(2).AddItem"上机日期"cmbfield(2).AddItem"上机时间"cmbfield(2).AddItem"操作教师"cmbfield(2).AddItem"教师类型"cmbfield(2).AddItem"机器号" cmbsign(0).AddItem"<"cmbsign(0).AddItem">"cmbsign(0).AddItem"="cmbsign(0).AddItem"<>" cmbsign(1).AddItem"<"cmbsign(1).AddItem">"cmbsign(1).AddItem"="cmbsign(1).AddItem"<>"  cmbsign(2).AddItem"<"cmbsign(2).AddItem">"cmbsign(2).AddItem"="cmbsign(2).AddItem"<>"' End SubPrivate Subchecktext(Index As Integer)    Dim ok1 As Boolean    Dim ok2 As Boolean    Dim ok3 As Boolean     If cmbfield(0) <> "" Andcmbsign(0) <> "" And Trim(txtinquire(0).Text) <>"" Then        ok1 = True    Else        ok1 = False    End If     If cmbfield(0) <> "" Andcmbsign(0) <> "" And Trim(txtinquire(0).Text) <>"" And cmbandor(0).Text <> "" And cmbfield(1).Text<> "" And cmbsign(1).Text <> "" AndTrim(txtinquire(1).Text) <> "" Then        ok2 = True    Else        ok2 = False    End If     If cmbfield(0) <> "" Andcmbsign(0) <> "" And Trim(txtinquire(0).Text) <> ""And cmbandor(0).Text <> "" And cmbfield(1).Text <>"" And cmbsign(1).Text <> "" AndTrim(txtinquire(1).Text) <> "" And cmbfield(2).Text <>"" And cmbsign(2).Text <> "" AndTrim(txtinquire(2).Text) <> "" Then        ok3 = True    Else        ok3 = False    End If     If Not TestTxt(cmbfield(Index).Text) Then        MsgBox "请选择字段名", vbOKOnly + vbInformation,"提示"        cmbfield(Index).SetFocus        Exit Sub    End If    If Not TestTxt(cmbsign(Index).Text) Then        MsgBox "请选择操作符", vbOKOnly + vbInformation,"提示"        cmbsign(Index).SetFocus        Exit Sub    End If    If Not TestTxt(txtinquire(Index).Text) Then        MsgBox "请输入要查询的内容", vbOKOnly +vbInformation, "提示"        txtinquire(Index).SetFocus        Exit Sub    End If End Sub

     到这了，所有的功能就能全部实现了，看下是不是很简单呢！ 如果谁有更好的方法欢迎指点批评！