frmqueryyibiao.frm

本程序用VB+SQL 2000实现,可以实现小区的住户资料,仪表资料,住户投宿,住户报修等数据的查询,修改,删除,统计和以报表形式打印输出.

         TabIndex        =   17
         Top             =   1320
         Width           =   615
      End
      Begin VB.Label Label4 
         Caption         =   "查找字段"
         Height          =   255
         Left            =   720
         TabIndex        =   16
         Top             =   1320
         Width           =   855
      End
      Begin VB.Label Label3 
         Caption         =   "逻辑关系"
         Height          =   255
         Left            =   6480
         TabIndex        =   15
         Top             =   360
         Width           =   735
      End
      Begin VB.Label Label2 
         Caption         =   "查找值"
         Height          =   255
         Left            =   4560
         TabIndex        =   14
         Top             =   360
         Width           =   615
      End
      Begin VB.Label Label1 
         Caption         =   "查找字段"
         Height          =   255
         Left            =   720
         TabIndex        =   13
         Top             =   360
         Width           =   855
      End
   End
End
Attribute VB_Name = "frmQueryYiBiao"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub cmdClose_Click()
   Unload Me
End Sub

Private Sub cmdQuery_Click()
'要求至少要有一个查询条件，而且查询条件要靠前写，即如果只有一个查询条件
'必须输在第一行，有2个查询条件，只能输在1，2两行上
   If Text1.Text = "" Then
       MsgBox "请输入至少一个查询条件，或把查询条件输入在前面的行上！", vbOKOnly + vbInformation, "注意"
       Text1.SetFocus
       Text2.Text = ""
       Text3.Text = ""
       Exit Sub
   End If
   If Text2.Text = "" And Not Text3.Text = "" Then
       MsgBox "请把查询条件输入在前面的行上！", vbOKOnly + vbInformation, "注意"
       Text2.SetFocus
       Text3.Text = ""
       Exit Sub
   End If
   '仪表编号和住户姓名查询字段的查询关系不能位 > 或 <，判断之
   If Not Text1.Text = "" And (Combo1.ListIndex = 0 Or Combo1.ListIndex = 1) And Not Combo2.ListIndex = 0 Then
      MsgBox "该字段的查找关系不能为 > 或 < ！", vbOKOnly + vbInformation, "注意"
      Combo2.SetFocus
      Exit Sub
   End If
   If Not Text2.Text = "" And (Combo4.ListIndex = 0 Or Combo4.ListIndex = 1) And Not Combo5.ListIndex = 0 Then
      MsgBox "该字段的查找关系不能为 > 或 < ！", vbOKOnly + vbInformation, "注意"
      Combo5.SetFocus
      Exit Sub
   End If
   If Not Text3.Text = "" And (Combo7.ListIndex = 0 Or Combo7.ListIndex = 1) And Not Combo8.ListIndex = 0 Then
      MsgBox "该字段的查找关系不能为 > 或 < ！", vbOKOnly + vbInformation, "注意"
      Combo8.SetFocus
      Exit Sub
   End If
   '以下分别对水电气3个选项卡处理查询条件
   If frmYiBiao.SSTab1.Tab = 0 Then
      '设置查询判断变量为真
      querywater = True
   ElseIf frmYiBiao.SSTab1.Tab = 1 Then
      querypower = True
   ElseIf frmYiBiao.SSTab1.Tab = 2 Then
      querygas = True
   End If
   '使用全局变量sqlYiBiao保存该查询表达式的sql 语句
   '如果查询字段是数字和日期，则需要转换text1.text的内容
   If Combo1.ListIndex = 0 Or Combo1.ListIndex = 1 Then
      sqlYiBiao = "where " & Combo1.Text & Combo2.Text & "'" & Text1.Text & "'"
   Else
      '查询字段是年份和月份，为整型，需用Val转换字符串为数字
      For i = 2 To 3
         If Combo1.ListIndex = i Then
            'Val 函数把字符串转换为数字
            sqlYiBiao = "where " & Combo1.Text & Combo2.Text & Val(Text1.Text)
         End If
      Next i
      '查询字段是仪表数据和费用等，为单精度数字，
      '需先用Val函数把字符串转换为数字，然后用CSng函数把数字转换为单精度数字
      For i = 4 To 7
        If Combo1.ListIndex = i Then
            'CSng函数把数字转换为单精度数字
            sqlYiBiao = "where " & Combo1.Text & Combo2.Text & CSng(Val(Text1.Text))
         End If
      Next i
      '查询字段是日期
      For j = 8 To 10
         If Combo1.ListIndex = j Then
            If IsDate(Text1.Text) = False Then
              MsgBox "输入的不是日期型内容，请输入这样的日期格式：2003-8-3！", vbOKOnly + vbInformation, "注意"
              Exit Sub
            End If
            'DateValue 函数把字符串转换为日期
            sqlYiBiao = "where " & Combo1.Text & Combo2.Text & "#" & DateValue(Text1.Text) & "#"
         End If
      Next j
   End If
   If Not Text2.Text = "" Then
      If Combo4.ListIndex = 0 Or Combo4.ListIndex = 1 Then
          'combo3.text是and or not 等逻辑字符，其前后需要各加上一个空格
         sqlYiBiao = sqlYiBiao & " " & Combo3.Text & " " & Combo4.Text & Combo5.Text & "'" & Text2.Text & "'"
      Else
         '查询字段是年份和月份，为整型，需用Val转换字符串为数字
         For i = 2 To 3
            If Combo4.ListIndex = i Then
               'Val 函数把字符串转换为数字
               sqlYiBiao = sqlYiBiao & " " & Combo3.Text & " " & Combo4.Text & Combo5.Text & Val(Text2.Text)
            End If
         Next i
          '查询字段是仪表数据和费用等，为单精度数字，
         '需先用Val函数把字符串转换为数字，然后用CSng函数把数字转换为单精度数字
         For i = 4 To 7
            If Combo4.ListIndex = i Then
               'CSng函数把数字转换为单精度数字
               sqlYiBiao = sqlYiBiao & " " & Combo3.Text & " " & Combo4.Text & Combo5.Text & CSng(Val(Text2.Text))
            End If
         Next i
         '查询字段是日期
         For j = 8 To 10
            If Combo4.ListIndex = j Then
               If IsDate(Text2.Text) = False Then
              MsgBox "输入的不是日期型内容，请输入这样的日期格式：2003-8-3！", vbOKOnly + vbInformation, "注意"
              Exit Sub
            End If
               'DateValue 函数把字符串转换为日期
               sqlYiBiao = "where " & " " & Combo3.Text & " " & Combo4.Text & Combo5.Text & "#" & DateValue(Text2.Text) & "#"
            End If
         Next j
      End If
   End If
   If Not Text3.Text = "" Then
      If Combo7.ListIndex = 0 Or Combo7.ListIndex = 1 Then
      sqlYiBiao = sqlYiBiao & " " & Combo6.Text & " " & Combo7.Text & Combo8.Text & "'" & Text3.Text & "'"
      Else
         For i = 2 To 3
            If Combo7.ListIndex = i Then
               'Val 函数把字符串转换为数字
               sqlYiBiao = sqlYiBiao & " " & Combo6.Text & " " & Combo7.Text & Combo8.Text & Val(Text3.Text)
            End If
         Next i
         For i = 4 To 7
            If Combo7.ListIndex = i Then
               sqlYiBiao = sqlYiBiao & " " & Combo6.Text & " " & Combo7.Text & Combo8.Text & CSng(Val(Text3.Text))
            End If
         Next i
         For j = 8 To 10
            If Combo7.ListIndex = j Then
               If IsDate(Text3.Text) = False Then
              MsgBox "输入的不是日期型内容，请输入这样的日期格式：2003-8-3！", vbOKOnly + vbInformation, "注意"
              Exit Sub
            End If
               'DateValue 函数把字符串转换为日期
               sqlYiBiao = sqlYiBiao & " " & Combo6.Text & " " & Combo7.Text & Combo8.Text & "#" & DateValue(Text3.Text) & "#"
            End If
         Next j
      End If
   End If
   frmYiBiao.Show
   Unload Me
End Sub

Private Sub Form_Load()
   '让窗体居中
   Dim X0 As Long
   Dim Y0 As Long
   X0 = Screen.Width
   Y0 = Screen.Height
   X0 = (X0 - Me.Width) / 2
   Y0 = (Y0 - Me.Height) / 2
   Me.Move X0, Y0
   Combo1.ListIndex = 0
   Combo2.ListIndex = 0
   Combo3.ListIndex = 0
   Combo4.ListIndex = 0
   Combo5.ListIndex = 0
   Combo6.ListIndex = 0
   Combo7.ListIndex = 0
   Combo8.ListIndex = 0
   Combo9.ListIndex = 0
   Text1.Text = ""
   Text2.Text = ""
   Text3.Text = ""
End Sub