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
| Public Sub calcul_nbr_mois()
Dim date1 As Date, date2 As Date
Dim varNbreMois As Integer
Dim rst_mois As New ADODB.Recordset
Dim i As Integer
Dim DateA As Integer
Dim DateB As Integer
Dim taBdd As Database
Dim date1_i As Date, date2_i As Date
Dim j As Integer
Dim monTableau() As String
Dim str_requete_stock As String
str_requete_stock = " SELECT Val(Mid([Transmission Date],7,4) & Mid([Transmission Date],4,2))AS AnMois,Count(FAC_ACTION.ID) AS NbrID,Sum(IIf([Clos] Is Null,Date()-[Transmission Date],[Clos]-[Transmission Date])) AS DMT FROM FAC_ACTION INNER JOIN FAC_SICLOP ON FAC_ACTION.ID= FAC_SICLOP.ID WHERE ((FAC_SICLOP.[Transmission Date]) < #" & date1_i & "#) AND (((FAC_ACTION.[Clos]) > #" & date1_i & "#) OR (FAC_ACTION.[Clos]) IS NULL) GROUP BY (Val(Mid([Transmission Date],7,4) & Mid([Transmission Date],4,2)))"
Set Db = Application.CurrentDb
date1 = Forms![f_Reporting]![DateA]
date2 = Forms![f_Reporting]![DateB]
MsgBox "Durée en nombre de mois : " & DateDiff("m", date1, date2)
varNbreMois = DateDiff("m", date1, date2)
For i = 0 To varNbreMois
date1_i = DateAdd("m", i, date1)
rst_mois.Open (str_requete_stock), CurrentProject.Connection
j = 0
Next i
ReDim monTableau(rst_requete_stock.RecordCount)
Do While Not rst_mois.EOF
monTableau(i) = rst_mois.Fields("NbrID") 'Cette ligne permet de récupérer la case correspondante à la ligne parcourue et au nom de champs que tu aura mit
rst_mois.MoveNext 'On passe à l'enregistrement suivant et on boucle
j = j + 1
Loop
End Sub |
Partager