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
| Sub Report()
Dim DB As DAO.Database
'Open acess database
chemin_BD = "X:\A\B.mdb"
Set DB = DAO.OpenDatabase(chemin_BD, False, False, "MS Access;PWD=X")
'Calculation of the start date
xlSheet.Range("F4").Value = d
xlSheet.Range("F3").Value = Format(d - Period, "mm/dd/yyyy")
'SQL query
SQL = "SELECT [Table1].[Product ID], [Table1].[Product Name], " & _
"[Table2].[Donnee1], [Table2].[Donnee2], [Table2].[Date] " & _
"FROM [Table1], [Table2] WHERE [Table1].[Product ID] = [Table2].[Product ID] AND [Table2].[Date] >=#" & Format(d, "mm/dd/yyyy") & "# " & _
"AND [Table2].[Date] <= #" & Format(d - Period, "mm/dd/yyyy") & "# " & _
"ORDER BY [Table1].[Product ID] ASC, [Table2].[Date] ASC"
Set rec = DB.OpenRecordset(SQL)
i=1
While xlSheet.Cells(i, 1).Value <> ""
'SQL query test
SQL1 = "SELECT MIN([Table2].[Donnee2]/(2 * [Table2].[Donnee1])) AS min FROM [Table2] WHERE [Table1].[Product ID] =" & i
Set rec = DB.OpenRecordset(SQL1)
SQL2 = "SELECT MAX([Table2].[Donnee2]/(2*[Table2].[Donnee1])) AS max FROM [Table] WHERE [Table1].[Product ID] =" & i
Set rec = DB.OpenRecordset(SQL2)
SQL3 = "SELECT AVG([Table2].[Donnee2]/(2*[Table2].[Donnee1])) AS avg FROM [Table] WHERE [Table1].[Product ID] =" & i
Set rec = DB.OpenRecordset(SQL3)
rec.MoveLast
rec.MoveFirst
If rec.Fields("Product ID").Value = xlSheet.Cells(i,2).Value And rec.Fields("Date").Value = Cells(i, 1).Value Then
xlSheet.Cells(i, 8).Value = rec.Fields("min").Value
xlSheet.Cells(i, 9).Value = rec.Fields("max").Value
xlSheet.Cells(i, 10).Value = rec.Fields("avg").Value
i = i + 1
Else
rec.Movenext
End If
Wend
End Sub |
Partager