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
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
| Private Sub Commande3_Click()
'varible dans laquelle on sotcke la date maxi saisie par l'utilisateur
Dim DateF As String
Dim Max As Date
Max = "01/02/2050"
'Définition des variables qui vont contenir les résultats des 4 dernières analyse
'objectif final : créer 4 requêtes de sélection qui vont venir alimenter des sous états
Dim Date1 As Date
Dim Date2 As Date
Dim Date3 As Date
Dim Date4 As Date
Dim QryDate1 As DAO.QueryDef
Dim RsDate1 As DAO.Recordset
Dim QryDate2 As DAO.QueryDef
Dim RsDate2 As DAO.Recordset
Dim QryDate3 As DAO.QueryDef
Dim RsDate3 As DAO.Recordset
Dim QryDate4 As DAO.QueryDef
Dim RsDate4 As DAO.Recordset
Dim sqlResDate1 As String
Dim QryResDate1 As DAO.QueryDef
Dim RsResDate1 As DAO.Recordset
Dim sqlResDate2 As String
Dim QryResDate2 As DAO.QueryDef
Dim RsResDate2 As DAO.Recordset
Dim sqlResDate3 As String
Dim QryResDate3 As DAO.QueryDef
Dim RsResDate3 As DAO.Recordset
Dim sqlResDate4 As String
Dim QryResDate4 As DAO.QueryDef
Dim RsResDate4 As DAO.Recordset
'Je récupère mon numéro d exploitation provenant d'un autre formulaire
Dim Num As String
Num = Module4.NumExploit2
'Je vérifie si une date a été saisie dans le champ
If (IsNull(Me.DateFin)) Then
DateF = Max
Else
DateF = Me.DateFin
End If
'DETERTMINATION DE LA DATE MAXIMUM
sqlDate1 = "SELECT Max(DateA) FROM Analyse WHERE ((DateA<""" & DateF & """))AND (((NumExploitation) = """ & Num & """)) "
DoCmd.DeleteObject acQuery, "ReqDate1"
'supprime la requête
CurrentDb.CreateQueryDef "ReqDate1", sqlDate1
'crée une nouvelle requete
Set QryDate1 = CurrentDb.QueryDefs("ReqDate1")
Set RsDate1 = QryDate1.OpenRecordset
'je récupoère ma date maximum
Date1 = RsDate1(0)
'je crée une requête qui affiche les analyses effectuées à la date maxi pour une exploitation donnée
sqlResDate1 = "SELECT DateA,NumVache,NbCellules,QteLait FROM Analyse"
sqlResDate1 = sqlResDate1 & " WHERE (((NumExploitation)=""" & Num & """))"
sqlResDate1 = sqlResDate1 & " AND (((DateA)>=" & DateValue(Date1) & "))AND (((DateA)<=#" & DateValue(Max) & "#))"
sqlResDate1 = sqlResDate1 & " ORDER BY NumVache,DateA"
DoCmd.DeleteObject acQuery, "ListeAnalDate1"
CurrentDb.CreateQueryDef "ListeAnalDate1", sqlResDate1
Set QryResDate1 = CurrentDb.QueryDefs("ListeAnalDate1")
Set RsResDate1 = QryResDate1.OpenRecordset
'Je refais la même démarche pour l'avant dernière date, l'avant avant dernière....
sqlDate2 = "SELECT Max(DateA) FROM Analyse WHERE (((DateA)<#" & DateValue(Date1) & "#)) AND (((NumExploitation) = """ & Num & """)) "
DoCmd.DeleteObject acQuery, "ReqDate2"
'supprime la requête
CurrentDb.CreateQueryDef "ReqDate2", sqlDate2
'crée une nouvelle requete
Set QryDate2 = CurrentDb.QueryDefs("ReqDate2")
Set RsDate2 = QryDate2.OpenRecordset
If (IsNull(RsDate2(0))) Then
'MsgBox ("pas d'autre analyse 2 ")
Else
Date2 = RsDate2(0)
sqlResDate2 = "SELECT DateA,NumVache,NbCellules,QteLait FROM Analyse"
sqlResDate2 = sqlResDate2 & " WHERE (((NumExploitation)=""" & Num & """))"
sqlResDate2 = sqlResDate2 & " AND (((DateA)>=#" & DateValue(Date2) & "#))AND (((DateA)<=#" & DateValue(Date1) & "#))"
sqlResDate2 = sqlResDate2 & " ORDER BY NumVache,DateA"
DoCmd.DeleteObject acQuery, "ListeAnalDate2"
CurrentDb.CreateQueryDef "ListeAnalDate2", sqlResDate2
Set QryResDate2 = CurrentDb.QueryDefs("ListeAnalDate2")
Set RsResDate2 = QryResDate2.OpenRecordset
sqlDate3 = "SELECT Max(DateA) FROM Analyse WHERE DateA<" & Date2 & " AND (((NumExploitation) = """ & Num & """)) "
DoCmd.DeleteObject acQuery, "ReqDate3"
'supprime la requête
CurrentDb.CreateQueryDef "ReqDate3", sqlDate3
'crée une nouvelle requete
Set QryDate3 = CurrentDb.QueryDefs("ReqDate3")
Set RsDate3 = QryDate3.OpenRecordset
If (IsNull(RsDate3(0))) Then
MsgBox ("pas d'autre analyse 3 ")
Else
Date3 = RsDate3(0)
sqlResDate3 = "SELECT DateA,NumVache,NbCellules,QteLait FROM Analyse"
sqlResDate3 = sqlResDate3 & " WHERE (((NumExploitation)=""" & Num & """))"
sqlResDate3 = sqlResDate3 & " AND (((DateA)>=#" & DateValue(Date3) & "#))AND (((DateA)<=#" & DateValue(Date2) & "#))"
sqlResDate3 = sqlResDate3 & " ORDER BY NumVache,DateA"
DoCmd.DeleteObject acQuery, "ListeAnalDate3"
CurrentDb.CreateQueryDef "ListeAnalDate3", sqlResDate3
Set QryResDate3 = CurrentDb.QueryDefs("ListeAnalDate3")
Set RsResDate3 = QryResDate3.OpenRecordset
sqlDate4 = "SELECT Max(DateA) FROM Analyse WHERE DateA<" & Date3 & " AND (((NumExploitation) = """ & Num & """)) "
DoCmd.DeleteObject acQuery, "ReqDate4"
'supprime la requête
CurrentDb.CreateQueryDef "ReqDate4", sqlDate4
'crée une nouvelle requete
Set QryDate4 = CurrentDb.QueryDefs("ReqDate4")
Set RsDate4 = QryDate4.OpenRecordset
Date4 = RsDate4(0)
If (IsNull(RsDate4(0))) Then
MsgBox ("pas d'autre analyse 4 ")
Else
sqlResDate4 = "SELECT DateA,NumVache,NbCellules,QteLait FROM Analyse"
sqlResDate4 = sqlResDate4 & " WHERE (((NumExploitation)=""" & Num & """))"
sqlResDate4 = sqlResDate4 & " AND (((DateA)>=#" & DateValue(Date4) & "#))AND (((DateA)<=#" & DateValue(Date3) & "#))"
sqlResDate4 = sqlResDate4 & " ORDER BY NumVache,DateA"
DoCmd.DeleteObject acQuery, "ListeAnalDate4"
CurrentDb.CreateQueryDef "ListeAnalDate4", sqlResDate4
Set QryResDate4 = CurrentDb.QueryDefs("ListeAnalDate4")
Set RsResDate4 = QryResDate4.OpenRecordset
End If
End If
End If
Num = ""
DoCmd.Close
End Sub |
