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
| Private Sub Report_Open(Cancel As Integer)
Dim MaDateDeb As String
Dim MaDateFin As String
Dim R1 As Object
Dim LaReq As String
Dim Cpt As Integer
If Forms!FEdit!ChxPer.Value = 1 Then
MaDateDeb = Short2Long(Forms("FEdit").TxtDeb)
MaDateFin = Short2Long(Forms("FEdit").TxtFin)
Else
MaDateDeb = Forms("FEdit").LabClotDeb.Caption
MaDateFin = Forms("FEdit").LabClotFin.Caption
End If
'*************************************************************************************
'************************ Création de la vue DEFICIT *********************************
'*************************************************************************************
'formate table DEFICIT
DoCmd.SetWarnings False
DoCmd.RunSQL ("DELETE FROM DEFICIT;")
'SELECTionne les clients non groupés
Set R1 = CurrentDb.OpenRecordset("" _
& "SELECT CliAS400, NomClient, IDDep, SUM(EntréeBr) AS [SomEB], " _
& "SUM(EntréeTr) AS [SomET], SUM(Sortie) AS [SomSor], SUM(A_broyer) AS [SomAbroyer]" _
& "FROM RETOUR, CLIENT " _
& "WHERE RETOUR.IDClient = CLIENT.IDClient " _
& "AND CLIENT.IDGr = 0 " _
& "AND DateRetour BETWEEN #" & CDat(MaDateDeb) & "# AND #" & CDat(MaDateFin) & "# " _
& "GROUP BY CliAS400, NomClient, IDDep;")
'INSERT ds vue
If R1.EOF = False Then
Cpt = 1
Do While R1.EOF = False
If R1.Fields(5) = 0 Then
LaReq = "Ø"
Else
LaReq = CStr(CLng(-(R1.Fields(4) - R1.Fields(5)) / R1.Fields(5) * 100)) & "%"
End If
DoCmd.RunSQL ("INSERT INTO DEFICIT VALUES (" & Cpt & ", '" & Appos(R1.Fields(0)) & "', '" _
& Appos(R1.Fields(1)) & "', '" & R1.Fields(2) & "', " & R1.Fields(3) & ", " _
& R1.Fields(4) & ", " & R1.Fields(5) & "," & R1.Fields(6) & ", " & (R1.Fields(4) - R1.Fields(5)) & ", '" _
& LaReq & "');")
R1.MoveNext
Cpt = Cpt + 1
Loop
R1.Close
End If
'SELECTionne clients groupés
Set R1 = CurrentDb.OpenRecordset("" _
& "SELECT NomGr, SUM(EntréeBr) AS SomEB, SUM(EntréeTr) AS SomET, SUM(Sortie) AS SomSor, " _
& "SUM(A_broyer) AS SomAbroyer" _
& "FROM RETOUR, CLIENT, GROUPE " _
& "WHERE RETOUR.IDClient = CLIENT.IDClient " _
& "AND CLIENT.IDGr = GROUPE.IDGr " _
& "AND CLIENT.IDGr <> 0 " _
& "AND DateRetour BETWEEN #" & CDat(MaDateDeb) & "# AND #" & CDat(MaDateFin) & "# " _
& "GROUP BY GROUPE.IDGr, NomGr;")
'INSERT ds vue
If R1.EOF = False Then
Do While R1.EOF = False
If R1.Fields(3) = 0 Then
LaReq = "Ø"
Else
LaReq = CStr(CLng(-(R1.Fields(2) - R1.Fields(3)) / R1.Fields(3) * 100)) & "%"
End If
DoCmd.RunSQL ("INSERT INTO DEFICIT VALUES (" & Cpt & ", '', '" _
& Appos(R1.Fields(0)) & "', '', " & R1.Fields(1) & ", " _
& R1.Fields(2) & ", " & R1.Fields(3) & "," & R1.Fields(4) & "," _
& (R1.Fields(2) - R1.Fields(3)) & ", '" _
& LaReq & "');")
R1.MoveNext
Cpt = Cpt + 1
Loop
R1.Close
End If
End Sub |
Partager