Option Explicit

Sub AddReferences()
'---------------------------------------------------------------------------------------
' Procedure : AddReferences
' Purpose   : Ïîäêëþ÷àåì ññûëêó íà ADO (Òà, ÷òî â ìåíþ Tools->References)
'---------------------------------------------------------------------------------------
    On Error Resume Next
    'Name:   ADODB
    'Description:   Microsoft ActiveX Data Objects 2.8 Library
    ThisWorkbook.VBProject.References.AddFromFile ("C:\Program Files\Common Files\System\ado\msado15.dll")
    ThisWorkbook.VBProject.References.AddFromGuid GUID:="{2A75196C-D9EB-4129-B803-931327F72D5C}", Major:=2, Minor:=8
End Sub

Function GetData(strPath As String, strField1 As String, strOperator1 As String, strCriterion1 As String, strField2 As String, strOperator2 As String, strCriterion2 As String, strField3 As String, strOperator3 As String, strCriterion3 As String) As Integer
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    Dim strSQL As String
                                                                            
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strPath & ";" & "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
   
   ' strSQL = "SELECT Count([" & strField1 & "]) FROM [Ëèñò3$A3:L1000] WHERE [" & strField1 & "]" & strOperator1 & "'" & strCriterion1 & "'" And "[" & strField2 & "]" & strOperator2 & "'" & strCriterion2 & "'" And "[" & strField3 & "]" & strOperator3 & "'" & strCriterion3 & "'"
     'ðàáîòàåò
    'strSQL = "SELECT  Count([" & strField1 & "]) FROM [Ëèñò3$A3:L1000] WHERE [" & strField1 & "]" & strOperator1 & "'" & strCriterion1 & "  && [" & strField2 & "]" & strOperator2 & "'" & strCriterion2 & " && [" & strField3 & "]" & strOperator3 & "'" & strCriterion3 & "'"
    
    strSQL = "SELECT Count([" & strField1 & "]) FROM [Ëèñò3$A3:L1000] WHERE [" & strField1 & "]" & strOperator1 & "'" & strCriterion1 & "'" & " And [" & strField2 & "]" & strOperator2 & "'" & strCriterion2 & "'" & " And [" & strField3 & "]" & strOperator3 & "'" & strCriterion3 & "'"
    'strSQL = "SELECT Count([" & strField1 & "]) FROM [Ëèñò3$A3:L1000] WHERE [" & strField1 & "]" & strOperator1 & "'" & strCriterion1 & "'" & " And [" & strField2 & "]" & strOperator2 & "'" & strCriterion2 & "'" & " And [" & strField3 & "]" & strOperator3 & "'" & strCriterion3 & "'"
    
    rst.Open strSQL, cnn

    GetData = rst.Fields(0)
    rst.Close
    cnn.Close

    Set rst = Nothing
    Set cnn = Nothing
End Function


Sub test()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    Dim strSQL As String
    Dim strCriterion1 As String
    Dim strCriterion2 As String
    Dim strCriterion3 As String
    
    

    Dim strField1 As String
    Dim strField2 As String
    Dim strField3 As String
    
    Dim strRange As String
    Dim strPath As String
    Dim strOperator1 As String
    Dim strOperator2 As String
    Dim strOperator3 As String
    Dim x As Integer
    
    strOperator1 = "="
    strOperator2 = ">"
    strOperator3 = ">"
    strPath = "c:\Gorokh\Report\Êíèãà4.xls"
    strField1 = "segment âèä UNIQA"
    strField2 = "Äàòà íàñòàííÿ ñòðàõîâîãî âèïàäêó"
    strField3 = "Äàòà ðåºñòðàöi¿ ñòðàõîâîãî âèïàäêó"
    strCriterion1 = "Casco"
    strCriterion2 = 39877
    strCriterion3 = 40180
    strRange = ""
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strPath & ";" & "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
    strSQL = "SELECT Count([" & strField1 & "]) FROM [Ëèñò3$A3:L1000] " & "WHERE [" & strField1 & "]" & strOperator1 & "'" & strCriterion1 & "' and [" & strField2 & "]" & strOperator2 & "'" & strCriterion2 & "' and [" & strField3 & "]" & strOperator3 & "'" & strCriterion3 & "'"
Debug.Print strSQL

    rst.Open strSQL, cnn
    x = rst.Fields(0)
Debug.Print x
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
End Sub

strCriterion1 = "Casco"
strCriterion2 = # 10 / 6 / 2009 #
strCriterion3 = # 1 / 27 / 2010 #

strSQL = "SELECT Count([" & strField1 & "]) FROM [Лист3$A3:L1000] " & "WHERE [" _
         & strField1 & "]" & strOperator1 & "'" & strCriterion1 & "' and [" _
         & strField2 & "]" & strOperator2 & "" & strCriterion2 & " and [" _
         & strField3 & "]" & strOperator3 & "" & strCriterion3 & ""

SELECT Count([segment вид UNIQA]) FROM [Лист3$A3:L1000] WHERE [segment вид UNIQA]='Casco' and [Дата настання страхового випадку]>= 10 / 6 / 2009  and [Дата реєстрацiї страхового випадку]>= 1 / 27 / 2010 
  3  

 
       strSQL1 = "SELECT Count([" & strField1 & "]) FROM [Лист3$A3:L1000] " & "WHERE [" _
        & strField1 & "]" & strOperator1 & "'Casco' and [" _
        & strField2 & "]" & strOperator2 & " #3/5/2009# and [" _
        & strField3 & "]" & strOperator3 & "#1/2/2010#"
         
        strSQL2 = "SELECT Sum([" & strField4 & "]) FROM [Лист3$A3:L1000] " & "WHERE [" _
        & strField1 & "]" & strOperator1 & "'Casco' and [" _
        & strField2 & "]" & strOperator2 & " #3/5/2009# and [" _
        & strField3 & "]" & strOperator3 & "#1/2/2010#"

    rst.Open strSQL1, cnn
    x1 = rst.Fields( 0 )
 
    Debug.Print strSQL1
    Debug.Print x1
     
    rst.Close
    cnn.Close
  
    Set rst = Nothing
    Set cnn = Nothing

End Sub

  

Sub test1()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    Dim strSQL1 As String

    Dim strCriterion1 As String

 
    Dim strField1 As String
    Dim strField2 As String

    
    Dim strRange As String
    Dim strPath As String
    
    Dim strOperator1 As String

    Dim x1 As Integer
    '--------------------------------------------------------------------------------------------------'
    strOperator1 = "="

    
    strField1 = "Сумма"
    strField2 = "Счет Б"
    strCriterion1 = "29096010605901"
    strPath = "C:\Documents and Settings\vgorokh\Рабочий стол\АВАНС\аванс.xlsm"
    strRange = ""
    
    Dim strConnectionString As String
    strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strPath & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"""
    cnn.Open strConnectionString
    
     'strSQL1 = "SELECT Sum([" & strField1 & "]) FROM [зачислено$A3:bu10000] " & "WHERE [" _
        & strField2 & "]" "& strOperator1 & " "& strCriterion1 &"
        
        
strSQL1 = "SELECT Sum([" & strField1 & "]) FROM [зачислено$A3:bu10000] " & "WHERE [" _
        & strField2 & "]" & strOperator1 & "& strCriterion1 &"
        
    rst.Open strSQL1, cnn
    
    x1 = rst.Fields(0)
    
    Debug.Print strSQL1
    Debug.Print x1
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    
End Sub