strSQL7 = "SELECT count(A.*) as kolvo from (select [" & strField5 & "] from [Ëèñò1$A3:AM65000] WHERE [" & strField1 & "] " & strOperator1 & " 'Casco' ) A"

 
Function GetData17(strPath As String, strField1 As String, strOperator1 As String, strCriterion1 As String, strField2 As String, strOperator2 As String, strCriterion2 As String, strOperator4 As String, strCriterion4 As String, strField3 As String, strOperator3 As String, strCriterion3 As String, strOperator5 As String, strCriterion5 As String, strField5 As String) As Currency


    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    Dim strSQL7 As String
    
    
    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
    
        
    '-----------------------------------------------------------------------------------------------------
    strSQL7 = "SELECT count(*) as kolvo from (select distinct [" & strField5 & "] from [Лист1$A3:AM65000] " + _
              "WHERE [" & strField1 & "] " & strOperator1 & " 'Casco' " + _
              "and [" & strField2 & "]" & strOperator2 & "#1/1/2011# and [" & strField2 & "] " & strOperator4 & "#1/1/2012# " + _
              "and [" & strField3 & "]   " & strOperator3 & "#1/1/2011# and [" & strField3 & "] " & strOperator5 & "#1/1/2012#)"
    rst.Open strSQL7, cnn
    Debug.Print rst( 0 )
    rst.Close
    cnn.Close
    '-----------------------------------------------------------------------------------------------------
    
    Set rst = Nothing
    Set cnn = Nothing
End Function

 '==================================================КОЛ_ВО=========================
Sub test17()
  
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strSQL7 As String
    Dim strConnectionString As String
    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    Dim strPath As String
    
    Dim strCriterion1 As String
    Dim strCriterion2 As String
    Dim strCriterion3 As String
    Dim strCriterion4 As String
    Dim strCriterion5 As String
    
    Dim strField1 As String
    Dim strField2 As String
    Dim strField3 As String
    Dim strField4 As String
    Dim strField5 As String
        
    Dim strRange As String
    
    Dim strOperator1 As String
    Dim strOperator2 As String
    Dim strOperator3 As String
    
    Dim strOperator4 As String
    Dim strOperator5 As String
    
    Dim x7 As Currency

    strOperator1 = "="
    strOperator2 = ">="
    strOperator3 = ">="
    strOperator4 = "<"
    strOperator5 = "<"
    
    strField1 = "segment вид UNIQA"
    strField2 = "Дата подiї"
    strField3 = "Дата реєстрацiї"
    strField4 = "Сума"
    strField5 = "Номер КЗ"
    
   
    strPath = "W:\Actuaries\Vika\Report\1.xlsm"

   ' для 2003 Excel
   ' strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";" & "Extended Properties=""Excel 8.0;IMEX=1"""
   ' cnn.Open strConnectionString
    
    ' для 2007 Excel
    strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strPath & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"""
    cnn.Open strConnectionString
    
    '=======================================================================================================
    'ЗАПРОС считает количество номеров КЗ
    '------------------------------------------------------------------------------------------------------
    'strSQL7 = "select Count(*) from [Лист1$A3:AM65000] " + _
     '         "WHERE [segment вид UNIQA] = 'Casco' " + _
     '         "and [Дата подiї]>= #1/1/2011# and [Дата реєстрацiї]>= #1/1/2011# " + _
     '         "and [Дата подiї]< #1/1/2012# and [Дата реєстрацiї]< #1/1/2012#"
   ' rst.Open strSQL7, cnn
   ' Debug.Print rst(0)
   ' rst.Close
    
    '====================================================================================================
    'ЗАПРОС считает неповторяющие номера КЗ
    '-----------------------------------------------------------------------------------------------------
    strSQL7 = "SELECT count(*) as kolvo from (select distinct [" & strField5 & "] from [Лист1$A3:AM65000] " + _
              "WHERE [" & strField1 & "] " & strOperator1 & " 'Casco' " + _
              "and [" & strField2 & "]" & strOperator2 & "#1/1/2011# and [" & strField2 & "] " & strOperator4 & "#1/1/2012# " + _
              "and [" & strField3 & "]   " & strOperator3 & "#1/1/2011# and [" & strField3 & "] " & strOperator5 & "#1/1/2012#)"
              
              Debug.Print strSQL7

    rst.Open strSQL7, cnn
    Debug.Print rst( 0 )
    rst.Close
    cnn.Close
    '=====================================================================================================
    
    Set rst = Nothing
    Set cnn = Nothing
    
End Sub