1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58
|
Private Sub ListeTypeAction_Click()
Dim varItm As Variant, intI As Integer
Dim Sql As String
Dim Compteur As Integer
CritSelectTypeAction = ""
Compteur = 0
Set frm = Forms![F-RdvAuto]
Set ctl = frm!ListeTypeAction
Sql = "SELECT [T-Action].CodeAction, [T-Action].CodeDossier, [T-Action].DateCreationAction, [T-Action].DateEcheanceAction, [T-Action].DateRealisationAction, [T-Action].MotifAction, [T-Dossier].CPDossier, DateDiff('d',[dateecheanceaction],cdate(" & Chr(34) & Forms![F-RdvAuto]!DateEcheance & Chr(34) & ")) AS retard"
Sql = Sql + " FROM [T-Action] INNER JOIN [T-Dossier] ON [T-Action].CodeDossier=[T-Dossier].CodeDossier"
Sql = Sql + " WHERE ("
For Each varItm In ctl.ItemsSelected
' MsgBox ctl.Column(1, varItm)
If Compteur > 0 Then
CritSelectTypeAction = CritSelectTypeAction & " OR ("
End If
CritSelectTypeAction = CritSelectTypeAction & "(([T-Action].DateRealisationAction) Is Null) AND (([T-Action].MotifAction)=" & Chr(34) & ctl.Column(0, varItm) & Chr(34) & "))"
Compteur = Compteur + 1
Next varItm
CritSelectTypeAction = CritSelectTypeAction + ";"
Sql = Sql + CritSelectTypeAction
Me!testREquete = Sql
'MsgBox Sql
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb
Set rs = db.OpenRecordset(Sql)
'nom des champs!
'CodeDossier, DateCreationAction, DateEcheanceAction, DateRealisationAction, MotifAction,
'CPDossier, Retard
strSQL = "SELECT [rs].CPDossier, [rs].MotifAction, Avg([rs].retard) AS MoyenneDeretard"
strSQL = strSQL + " From [rs]"
strSQL = strSQL + " GROUP BY [rs].CPDossier, [rs].MotifAction"
strSQL = strSQL + " ORDER BY Avg([rs].retard) DESC;"
Set rs2 = db.OpenRecordset(strSQL)
MsgBox rs2!MotifAction
rs2.Close
rs.Close
End Sub |
Partager