dilerim işinize yarar.
Maximum pozisyon
Public Function MaxPozisyon(Dgr As String) As String
Dim Sql As String 'Afghanistan
Dim ADO_RS As ADODB.Recordset
MaxPozisyon = "KAYIT YOK"
SQL = "SELECT TOP 1 [F12], count([F12])" & _
"FROM [Futbolcular$A3:L] " & _
"WHERE ([F4]='" & Dgr & "') " & _
"GROUP BY [F12] " & _
"ORDER BY count([F12]) DESC"
Set ADO_RS = New ADODB.Recordset
ADO_RS.Open SQL, ADO_CN, 3, 1
If ADO_RS.RecordCount = 0 Then GoTo skipfile:
ADO_RS.MoveLast
ADO_RS.MoveFirst
With ADO_RS
Do Until .EOF
MaxPozisyon2 = MaxPozisyon2 & "; " & ADO_RS(0)
.MoveNext
DoEvents
Loop
End With
MaxPozisyon = Trim(Mid(MaxPozisyon2, 2))
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
skipfile:
ADO_RS.Close
Set ADO_RS = Nothing
'Set WS = Nothing
End FunctionPublic Function EnYasli(Dgr As String) As String
Dim Sql As String 'Afghanistan
Dim ADO_RS As ADODB.Recordset
EnYasli = "KAYIT YOK"
Baglan
SQL = "SELECT Yasli.F4, Yasli.EnÇokF3, [Futbolcular$A3:l].F2 " & _
"FROM (SELECT [Futbolcular$A3:L].F4, Max([Futbolcular$A3:L].F3) AS EnÇokF3 " & _
"FROM [Futbolcular$A3:L] " & _
"GROUP BY [Futbolcular$A3:l].F4) as Yasli " & _
"INNER JOIN [Futbolcular$A3:L] ON ([Futbolcular$A3:l].F4 = Yasli.F4) AND " & _
"(Yasli.EnÇokF3 = [Futbolcular$A3:l].F3) AND (Yasli.F4 = [Futbolcular$A3:l].F4) AND (Yasli.EnÇokF3 = [Futbolcular$A3:l].F3) " & _
"WHERE (((Yasli.F4)='" & Dgr & "')) " & _
"ORDER BY Yasli.F4;"
Set ADO_RS = New ADODB.Recordset
ADO_RS.Open SQL, ADO_CN, 3, 1
If ADO_RS.RecordCount = 0 Then GoTo skipfile:
ADO_RS.MoveLast
ADO_RS.MoveFirst
With ADO_RS
Do Until .EOF
EnYasli2 = EnYasli2 & "; " & ADO_RS(2) & " - " & ADO_RS(1)
' Debug.Print EnYasli2
.MoveNext
DoEvents
Loop
End With
EnYasli = Trim(Mid(EnYasli2, 2))
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
skipfile:
ADO_RS.Close
Set ADO_RS = Nothing
End Function
