frmbasequery.frm

一个实用的房产信息管理系统

    sql = sql & "xinm like '*" + Trim(cboXinM) + "*' "
    bChoice = True
End If
If Len(Trim(cboXinB)) <> 0 Then
    If bChoice Then
        sql = sql & "and yanjs='" + Trim(cboXinB) + "' "
    Else
        sql = sql & "yanjs='" + Trim(cboXinB) + "' "
        bChoice = True
    End If
End If
If Len(Trim(cboXueL)) <> 0 Then
    If bChoice Then
        sql = sql & "and dax='" + Trim(cboXueL) + "' "
    Else
        sql = sql & "dax='" + Trim(cboXueL) + "' "
        bChoice = True
    End If
End If
If Len(Trim(cboMinZ)) <> 0 Then
    If bChoice Then
        sql = sql & "and gongh='" + Trim(cboMinZ) + "' "
    Else
        sql = sql & "gongh='" + Trim(cboMinZ) + "' "
        bChoice = True
    End If
End If
If Len(Trim(cboZhufXZ)) <> 0 Then
    If bChoice Then
        sql = sql & "and hunyzk='" + HunYIn(Trim(cboZhufXZ)) + "' "
    Else
        sql = sql & "hunyzk='" + HunYIn(Trim(cboZhufXZ)) + "' "
        bChoice = True
    End If
End If
If Len(Trim(cboYuanX)) <> 0 Then
    If bChoice Then
        sql = sql & "and danw='" + Trim(cboYuanX) + "' "
    Else
        sql = sql & "danw='" + Trim(cboYuanX) + "' "
        bChoice = True
    End If
End If
If Len(Trim(cboZhiC)) <> 0 Then
    If bChoice Then
        sql = sql & "and zhic='" + CStr(ZhiCIn(cboZhiC)) + "' "
    Else
        sql = sql & "zhic='" + CStr(ZhiCIn(cboZhiC)) + "' "
        bChoice = True
    End If
End If
If Len(Trim(cboZhiW)) <> 0 Then
    If bChoice Then
        sql = sql & "and zhiw='" + Trim(CStr(ZhiCIn(cboZhiW))) + "' "
    Else
        sql = sql & "zhiw='" + Trim(CStr(ZhiCIn(cboZhiW))) + "' "
        bChoice = True
    End If
End If
If Len(Trim(cboXin)) <> 0 Then
    If bChoice Then
        sql = sql & "and xinb='" + Trim(cboXin) + "' "
    Else
        sql = sql & "xinb='" + Trim(cboXin) + "' "
        bChoice = True
    End If
End If
'数字型查询
If Len(Trim(cboGongZSJ)) <> 0 Then
    If Len(Trim(txtGongZSJ)) <> 0 Then
        If bChoice Then
            sql = sql & "and juzmj" & Trim(cboGongZSJ) & "" + Trim(txtGongZSJ) + " "
        Else
            sql = sql & "juzmj" & Trim(cboGongZSJ) & "" + Trim(txtGongZSJ) + " "
            bChoice = True
        End If
    End If
End If
If Len(Trim(cboSiZSJ)) <> 0 Then
    If Len(Trim(txtSiZSJ)) <> 0 Then
        If bChoice Then
            sql = sql & "and renjmj" & Trim(cboSiZSJ) & "" + Trim(txtSiZSJ) + " "
        Else
            sql = sql & "renjmj" & Trim(cboSiZSJ) & "" + Trim(txtSiZSJ) + " "
            bChoice = True
        End If
    End If
End If
If Len(Trim(cboFangWMJ)) <> 0 Then
    If Len(Trim(txtFangWMJ)) <> 0 Then
        If bChoice Then
            sql = sql & "and jianzmj" & Trim(cboFangWMJ) & "" + Trim(txtFangWMJ) + " "
        Else
            sql = sql & "jianzmj" & Trim(cboFangWMJ) & "" + Trim(txtFangWMJ) + " "
            bChoice = True
        End If
    End If
End If
If Len(Trim(cboGongJJ)) <> 0 Then
    If Len(Trim(txtGongJJ)) <> 0 Then
        If bChoice Then
            sql = sql & "and gongjj" & Trim(cboGongJJ) & "" + Trim(txtGongJJ) + " "
        Else
            sql = sql & "gongjj" & Trim(cboGongJJ) & "" + Trim(txtGongJJ) + " "
            bChoice = True
        End If
    End If
End If
'时间型查询
If mskCSNYfrom = "__/__/____" And mskCSNYto <> "__/__/____" Then
    If Not IsDate(mskCSNYto) Then
        MsgBox "时间形式不正确！", vbExclamation + vbOKOnly, "信息"
        mskCSNYto = "__/__/____"
        Exit Sub
    End If
    If bChoice Then
        sql = sql + "and laixgz <=" + "#" + "" + Trim(mskCSNYto.Text) + "" + "#" + " "
    Else
        sql = sql + "laixgz <=" + "#" + "" + Trim(mskCSNYto.Text) + "" + "#" + " "
        bChoice = True
    End If
End If
If mskCSNYfrom.Text <> "__/__/____" And mskCSNYto = "__/__/____" Then
    If Not IsDate(mskCSNYfrom) Then
        MsgBox "时间形式不正确！", vbExclamation + vbOKOnly, "信息"
        mskCSNYfrom = "__/__/____"
        Exit Sub
    End If
    If bChoice Then
        sql = sql + "and laixgz>=" + "#" + "" + Trim(mskCSNYfrom.Text) + "" + "#" + " "
    Else
        sql = sql + "laixgz>=" + "#" + "" + Trim(mskCSNYfrom.Text) + "" + "#" + " "
        bChoice = True
    End If
End If
If mskCSNYfrom <> "__/__/____" And mskCSNYto <> "__/__/____" Then
    If Not IsDate(mskCSNYto) Then
        MsgBox "时间形式不正确！", vbExclamation + vbOKOnly, "信息"
        mskCSNYto = "__/__/____"
        Exit Sub
    End If
     If Not IsDate(mskCSNYfrom) Then
        MsgBox "时间形式不正确！", vbExclamation + vbOKOnly, "信息"
        mskCSNYfrom = "__/__/____"
        Exit Sub
    End If
    If bChoice Then
        sql = sql + "and laixgz between" + " #" + "" + Trim(mskCSNYfrom.Text) + "" + "#" + " and " + "#" + "" + Trim(mskCSNYto.Text) + "" + "#" + " "
    Else
        sql = sql + "laixgz between" + " #" + "" + Trim(mskCSNYfrom.Text) + "" + "#" + " and " + "#" + "" + Trim(mskCSNYto.Text) + "" + "#" + " "
        bChoice = True
    End If
End If
'2
If mskFrom2 = "__/__/____" And mskTo2 <> "__/__/____" Then
    If Not IsDate(mskTo2) Then
        MsgBox "时间形式不正确！", vbExclamation + vbOKOnly, "信息"
        mskTo2 = "__/__/____"
        Exit Sub
    End If
    If bChoice Then
        sql = sql + "and chusny <=" + "#" + "" + Trim(mskTo2.Text) + "" + "#" + " "
    Else
        sql = sql + "chusny <=" + "#" + "" + Trim(mskTo2.Text) + "" + "#" + " "
        bChoice = True
    End If
End If
If mskFrom2 <> "__/__/____" And mskTo2 = "__/__/____" Then
    If Not IsDate(mskFrom2) Then
        MsgBox "时间形式不正确！", vbExclamation + vbOKOnly, "信息"
        mskFrom2 = "__/__/____"
        Exit Sub
    End If
    If bChoice Then
        sql = sql + "and chusny>=" + "#" + "" + Trim(mskFrom2.Text) + "" + "#" + " "
    Else
        sql = sql + "chusny>=" + "#" + "" + Trim(mskFrom2.Text) + "" + "#" + " "
        bChoice = True
    End If
End If
If mskFrom2 <> "__/__/____" And mskTo2 <> "__/__/____" Then
    If Not IsDate(mskTo2) Then
        MsgBox "时间形式不正确！", vbExclamation + vbOKOnly, "信息"
        mskTo2 = "__/__/____"
        Exit Sub
    End If
    If Not IsDate(mskFrom2) Then
        MsgBox "时间形式不正确！", vbExclamation + vbOKOnly, "信息"
        mskFrom2 = "__/__/____"
        Exit Sub
    End If
    If bChoice Then
        sql = sql + "and chusny between" + " #" + "" + Trim(mskFrom2.Text) + "" + "#" + " and " + "#" + "" + Trim(mskTo2.Text) + "" + "#" + " "
    Else
        sql = sql + "chusny between" + " #" + "" + Trim(mskFrom2.Text) + "" + "#" + " and " + "#" + "" + Trim(mskTo2.Text) + "" + "#" + " "
        bChoice = True
    End If
End If
'3
If mskFrom3 = "__/__/____" And mskTo3 <> "__/__/____" Then
    If Not IsDate(mskTo3) Then
        MsgBox "时间形式不正确！", vbExclamation + vbOKOnly, "信息"
        mskTo3 = "__/__/____"
        Exit Sub
    End If
    If bChoice Then
        sql = sql + "and canjgmgz <=" + "#" + "" + Trim(mskTo3.Text) + "" + "#" + " "
    Else
        sql = sql + "canjgmgz <=" + "#" + "" + Trim(mskTo3.Text) + "" + "#" + " "
        bChoice = True
    End If
End If
If mskFrom3 <> "__/__/____" And mskTo3 = "__/__/____" Then
    If Not IsDate(mskFrom3) Then
        MsgBox "时间形式不正确！", vbExclamation + vbOKOnly, "信息"
        mskFrom3 = "__/__/____"
        Exit Sub
    End If
    If bChoice Then
        sql = sql + "and canjgmgz>=" + "#" + "" + Trim(mskFrom3.Text) + "" + "#" + " "
    Else
        sql = sql + "canjgmgz>=" + "#" + "" + Trim(mskFrom3.Text) + "" + "#" + " "
        bChoice = True
    End If
End If
If mskFrom3 <> "__/__/____" And mskTo3 <> "__/__/____" Then
    If Not IsDate(mskTo3) Then
        MsgBox "时间形式不正确！", vbExclamation + vbOKOnly, "信息"
        mskTo3 = "__/__/____"
        Exit Sub
    End If
    If Not IsDate(mskFrom3) Then
        MsgBox "时间形式不正确！", vbExclamation + vbOKOnly, "信息"
        mskFrom3 = "__/__/____"
        Exit Sub
    End If
    If bChoice Then
        sql = sql + "and canjgmgz between" + " #" + "" + Trim(mskFrom3.Text) + "" + "#" + " and " + "#" + "" + Trim(mskTo3.Text) + "" + "#" + " "
    Else
        sql = sql + "canjgmgz between" + " #" + "" + Trim(mskFrom3.Text) + "" + "#" + " and " + "#" + "" + Trim(mskTo3.Text) + "" + "#" + " "
        bChoice = True
    End If
End If
'
'职务类别
If Len(Trim(cboZhiJ)) <> 0 Then
    
    Dim recZhiw As Recordset
    Set recZhiw = dbEstate.OpenRecordset("select distinct id from zhiw where lb='" + Trim(cboZhiJ) + "'", dbOpenSnapshot)
    If recZhiw.RecordCount > 0 Then
        If bChoice Then
            sql = sql & "and ("
        Else
            sql = sql & " ("
            bChoice = True
        End If
        sql = sql & "zhiw='" + CStr(recZhiw!ID) + "' "
        While Not recZhiw.EOF
          sql = sql & "or zhiw='" + CStr(recZhiw!ID) + "' "
          recZhiw.MoveNext
        Wend
        sql = sql & ")"
    End If
    
End If

If Not bChoice Then
    MsgBox "没有选择条件！", vbExclamation + vbOKOnly, "信息"
Else
    Set recJiBQK = dbEstate.OpenRecordset(sql, dbOpenSnapshot)
    If recJiBQK.RecordCount > 0 Then recJiBQK.MoveLast
    StatusBar1.Panels.Item(1).Text = "查询结果:共有" & CStr(recJiBQK.RecordCount) & "条记录"
    Set Data1.Recordset = recJiBQK
    MakeGrid
End If
End Sub

Private Sub Form_Load()
Dim recFind As Recordset
Data1.DatabaseName = App.Path & "\dbestate.mdb"

'添加Combo框的内容

Set recFind = dbEstate.OpenRecordset("select distinct xinm from jiaozgzfxx", dbOpenSnapshot)
recFind.MoveLast
recFind.MoveFirst
While Not recFind.EOF
  If Not IsNull(recFind!xinm) And Len(recFind!xinm) <> 0 Then cboXinM.AddItem recFind!xinm
  recFind.MoveNext
Wend

Set recFind = dbEstate.OpenRecordset("select distinct dax from jiaozgzfxx", dbOpenSnapshot)
recFind.MoveLast
recFind.MoveFirst
While Not recFind.EOF
  If Not IsNull(recFind!dax) And Len(recFind!dax) <> 0 Then cboXueL.AddItem recFind!dax
  recFind.MoveNext
Wend

Set recFind = dbEstate.OpenRecordset("select distinct gongh from jiaozgzfxx", dbOpenSnapshot)
recFind.MoveLast
recFind.MoveFirst
While Not recFind.EOF
  If Not IsNull(recFind!GongH) And Len(recFind!GongH) <> 0 Then cboMinZ.AddItem recFind!GongH
  recFind.MoveNext
Wend

Set recFind = dbEstate.OpenRecordset("select distinct mc from zhiw", dbOpenSnapshot)
recFind.MoveLast
recFind.MoveFirst
While Not recFind.EOF
  If Not IsNull(recFind!mc) And Len(recFind!mc) <> 0 Then cboZhiW.AddItem recFind!mc
  recFind.MoveNext
Wend

Set recFind = dbEstate.OpenRecordset("select distinct danw from jiaozgzfxx", dbOpenSnapshot)
recFind.MoveLast
recFind.MoveFirst
While Not recFind.EOF
  If Not IsNull(recFind!danw) And Len(recFind!danw) <> 0 Then cboYuanX.AddItem recFind!danw
  recFind.MoveNext
Wend

Set recFind = dbEstate.OpenRecordset("select distinct mc from zhic", dbOpenSnapshot)
recFind.MoveLast
recFind.MoveFirst
While Not recFind.EOF
  If Not IsNull(recFind!mc) And Len(recFind!mc) <> 0 Then cboZhiC.AddItem recFind!mc
  recFind.MoveNext
Wend

Set recFind = dbEstate.OpenRecordset("select distinct lb from zhiw", dbOpenSnapshot)
recFind.MoveLast
recFind.MoveFirst
While Not recFind.EOF
  If Not IsNull(recFind!lb) And Len(recFind!lb) <> 0 Then cboZhiJ.AddItem recFind!lb
  recFind.MoveNext
Wend

Set recFind = dbEstate.OpenRecordset("select distinct yanjs from jiaozgzfxx", dbOpenSnapshot)
recFind.MoveLast
recFind.MoveFirst
While Not recFind.EOF
  If Not IsNull(recFind!yanjs) And Len(recFind!yanjs) <> 0 Then cboXinB.AddItem recFind!yanjs
  recFind.MoveNext
Wend
End Sub

'设置Mskgrid的参数
Private Sub MakeGrid()
End Sub