【VBA案例008】多条件查询

1
Sub 方法一()
2

3
    Dim i, j, k
4
    Dim ar, br()
5

6
    With Sheet2
7
        ar = .Range("a1:f" & .[a65536].End(3).Row)
8
    End With
9

10
    ReDim br(1 To UBound(ar), 1 To UBound(ar, 2))
11

12
    Dim customer, product, startDate As Date, endDate As Date
13
    With Sheet1
14
        customer = IIf(.[b2] = "", "", "," & .[b2] & ",")
15
        product = IIf(.[d2] = "", "", "," & .[d2] & ",")
16
        startDate = IIf(.[f2] = "", #1/1/1900#, .[f2])
17
        endDate = IIf(.[h2] = "", #1/1/2400#, .[h2])
18
    End With
19

20
    For i = 2 To UBound(ar)
21
        If ar(i, 1) >= startDate And ar(i, 1) <= endDate Then
22
            If InStr("," & ar(i, 2) & ",", customer) > 0 Then
23
                If InStr("," & ar(i, 3) & ",", product) > 0 Then
24
                    k = k + 1
25
                    For j = 1 To UBound(br, 2)
26
                        br(k, j) = ar(i, j)
27
                    Next j
28
                End If
29
            End If
30
        End If
31
    Next i
32

33
    Sheet1.[a5:f65536].ClearContents
34
    If k > 0 Then
35
        Sheet1.[a5].Resize(k, UBound(br, 2)) = br
36
    End If
37

38
End Sub

1
Sub SQL查询()
2
    '定义变量
3
    Dim cnn, rst, SQL$
4
    Dim i, j, k
5
    Set cnn = CreateObject("adodb.connection") '创建数据库连接
6
    Set rst = CreateObject("adodb.recordset") '创建一个数据集保存数据
7

8
    '设置数据库连接
9
    If Val(Application.Version) < 12 Then
10
        cnn.Open "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties='Excel 8.0;HDR=yes';Data Source=" & ThisWorkbook.FullName
11
    Else
12
        cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=yes';Data Source=" & ThisWorkbook.FullName
13
    End If
14

15
    Dim customer, product, startDate As Date, endDate As Date
16
    With Sheet1
17
        customer = IIf(.[b2] = "", "", "," & .[b2] & ",")
18
        product = IIf(.[d2] = "", "", "," & .[d2] & ",")
19
        startDate = IIf(.[f2] = "", #1/1/1900#, .[f2])
20
        endDate = IIf(.[h2] = "", #1/1/2400#, .[h2])
21
    End With
22

23
    '设置SQL语句
24
    SQL = "select * from [产品信息$A1:F22] where 日期>=#" & startDate & "# and 日期<=#" & endDate & "# and instr(','&客户&',','" & customer & "')>0 and instr(','&产品&',','" & product & "')>0" '
25

26
    'SQL结果处理
27
    Set rst = cnn.Execute(SQL)
28

29
    Sheet1.[a5:f65536].ClearContents
30
    Sheet1.Range("a5").CopyFromRecordset rst
31
'    For i = 1 To rst.Fields.Count
32
'        Cells(1, i + 5) = rst.Fields(i - 1).Name
33
'    Next
34

35
    rst.Close
36
    cnn.Close '关闭数据库连接
37
    Set rst = Nothing
38
    Set cnn = Nothing '将cnn从内存中删除
39
End Sub