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 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77
| Function Equation_CD_Z2N()
DoCmd.SetWarnings False
Dim Date_Now As Date
Dim temps_ref As Date
Dim temps_next As Date
Dim rame As String
Dim Date_ As Date
Date_Now = Format(Now(), "dd/mm/yy")
date_maj = Forms!analyse!Z2N_date
date_dernière = Format(DateAdd("d", 1, date_maj), "dd/mm/yyyy")
SQL_Def = "SELECT [Parc Z2N].Rame_Complète"
SQL_Def = SQL_Def & " FROM defauts_NAT INNER JOIN [Parc Z2N] ON defauts_NAT.Véhicule = [Parc Z2N].véhicule"
SQL_Def = SQL_Def & " WHERE ((defauts_NAT.[date] < #" & Date_Now & "#) AND (defauts_NAT.[date] >= #" & date_dernière & "#) AND ((defauts_NAT.code_defaut = '" & "AC-109" & "') OR (defauts_NAT.code_defaut = '" & "AC-102" & "'))) "
SQL_Def = SQL_Def & " GROUP BY [Parc Z2N].Rame_Complète"
SQL_Def = SQL_Def & " ORDER BY defauts_NAT.[Date] DESC,[Parc Z2N].Rame_complète DESC;"
Set rs_SQL_Def = CurrentDb.OpenRecordset(SQL_Def)
If Not rs_SQL_Def.EOF Then
rs_SQL_Def.MoveLast
Z = rs_SQL_Def.RecordCount
rs_SQL_Def.MoveFirst
Do While Not rs_SQL_Def.EOF
rame = rs_SQL_Def("Rame_complète").Value
SQL_Def2 = "SELECT defauts_NAT.[Date]"
SQL_Def2 = SQL_Def2 & " FROM defauts_NAT INNER JOIN [Parc Z2N] ON defauts_NAT.Véhicule = [Parc Z2N].véhicule"
SQL_Def2 = SQL_Def2 & " WHERE ((defauts_NAT.[date] < #" & Date_Now & "#) AND (defauts_NAT.[date] >= #" & date_dernière & "#) AND ((defauts_NAT.code_defaut = '" & "AC-109" & "') OR (defauts_NAT.code_defaut = '" & "AC-102" & "'))) AND [Parc Z2N].Rame_Complète = '" & rame & "' "
SQL_Def2 = SQL_Def2 & " GROUP BY defauts_NAT.[Date]"
SQL_Def2 = SQL_Def2 & " ORDER BY defauts_NAT.[Date] DESC;"
Set rs_SQL_Def2 = CurrentDb.OpenRecordset(SQL_Def2)
If Not rs_SQL_Def2.EOF Then
rs_SQL_Def2.MoveLast
y = rs_SQL_Def2.RecordCount
rs_SQL_Def2.MoveFirst
Do While Not rs_SQL_Def2.EOF
Date_ = rs_SQL_Def2("Date").Value
SQL_Def3 = "SELECT [Parc Z2N].Rame_Complète, defauts_NAT.code_defaut, defauts_NAT.Date, defauts_NAT.Première"
SQL_Def3 = SQL_Def3 & " FROM defauts_NAT INNER JOIN [Parc Z2N] ON defauts_NAT.Véhicule = [Parc Z2N].véhicule"
SQL_Def3 = SQL_Def3 & " GROUP BY [Parc Z2N].Rame_Complète, defauts_NAT.code_defaut, defauts_NAT.Date, defauts_NAT.Première"
SQL_Def3 = SQL_Def3 & " HAVING ((((defauts_NAT.code_defaut)= '" & AC - 109 & "' ) OR ((defauts_NAT.code_defaut)= '" & AC - 102 & "' )) AND (defauts_NAT.[Date] = #" & Date_ & "#)) "
SQL_Def3 = SQL_Def3 & " ORDER BY [Parc Z2N].Rame_Complète DESC , defauts_NAT.Date DESC , defauts_NAT.Première DESC;"
Set rs_SQL_Def3 = CurrentDb.OpenRecordset(SQL_Def3)
If Not rs_SQL_Def3.EOF Then
rs_SQL_Def3.MoveLast
x = rs_SQL_Def3.RecordCount
rs_SQL_Def3.MoveFirst
'--- GROS TROU POUR TESTER 2 PAR 2 ---
Else
End If
rs_SQL_Def2.MoveNext
Loop
Else
End If
rs_SQL_Def.MoveNext
Loop
Else
End If
End Function |
Partager