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(select distinct ([" & strField5 & "]) from [Лист1$A3:AM65000]" & "WHERE [" _
& strField1 & "]" & strOperator1 & "'" & strCriterion1 & "' and [" _
& strField2 & "]" & strOperator2 & " #1/1/2011# and [" _
& strField3 & "]" & strOperator3 & " #1/1/2011# and [" _
& strField2 & "]" & strOperator4 & " #1/1/2012# and [" _
& strField3 & "]" & strOperator5 & " #1/1/2012# )"
     
    rst.Open strSQL7, cnn
    GetData17 = rst.Fields( 0 )
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
End Function

'------------------------------------ GetData8----------------------------------------------------------------------------------------

 Sub test17()
    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 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 strPath 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 = "Дата події"
    strField3 = "Дата реєстрації"
    strField4 = "Сума"
    strField5 = "Номер КЗ"
    
     strPath = "W:\Actuaries\Vika\Report\âûïëàòû\PaymentCompens_01-04-11_30-04-11.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
    
      Debug.Print strSQL7
      Debug.Print x7
      
strSQL7 = "SELECT Count (distinct ([" & strField5 & "] from [Лист1$A3:AM65000])" & "WHERE [" _
& strField1 & "]" & strOperator1 & "'Casco' and [" _
& strField2 & "]" & strOperator2 & " #1/1/2011# and [" _
& strField3 & "]" & strOperator3 & " #1/1/2011# and [" _
& strField2 & "]" & strOperator4 & " #1/1/2012# and [" _
& strField3 & "]" & strOperator5 & " #1/1/2012# )"
    
   'rst.Open strSQL7, cnn
   ' x7 = rst.Fields(0)

  ' rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    
End Sub

 strSQL7 = "SELECT count(*) as kolvo  from (select distinct [" & strField5 & "] from [Лист1$A3:AM65000])" & "WHERE [" & strField1 & "] " & strOperator1 & " 'Casco' "

SELECT count(*) as kolvo  from (select distinct [Номер КЗ] from [Лист1$A3:AM65000])WHERE [segment вид UNIQA] = 'Casco' 
  0