1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
| Dim Sql As String 'déclare la variable Sql
Sql = "TRANSFORM Sum(nz([dbo_VLIGNES_FACTVTE]![NLVTOTNET],0)) AS Total"
Sql = Sql & "SELECT Format([dbo_VLIGNES_FACTVTE]![DLVINVOICEDAT],'yyyy') AS Année,"
Sql = Sql & "dbo_VLIGNES_FACTVTE.ALVNATURE AS Nature, dbo_VLIGNES_FACTVTE.ALVREP1 AS Cial"
Sql = Sql & "FROM dbo_VLIGNES_FACTVTE"
Sql = Sql & "WHERE (((Format([dbo_VLIGNES_FACTVTE]![DLVINVOICEDAT],'yyyy'))=Format(Date(),'yyyy')"
Sql = Sql & "Or (Format([dbo_VLIGNES_FACTVTE]![DLVINVOICEDAT],'yyyy'))=Format(Date(),'yyyy')-1)"
Sql = Sql & "AND ((dbo_VLIGNES_FACTVTE.ALVREP1)= " & Me.Représentant & ""
Sql = Sql & "AND ((Format([dbo_VLIGNES_FACTVTE]![DLVINVOICEDAT],'mm'))<= " & Format(Me.txtFin, "mm") & "))"
Sql = Sql & "GROUP BY Format([dbo_VLIGNES_FACTVTE]![DLVINVOICEDAT],'yyyy'),"
Sql = Sql & "dbo_VLIGNES_FACTVTE.ALVNATURE, dbo_VLIGNES_FACTVTE.ALVREP1"
Sql = Sql & "PIVOT Format([dbo_VLIGNES_FACTVTE]![DLVINVOICEDAT],'mm');"
DoCmd.DeleteObject acQuery, "R110_Gen_Temp_AC1" 'supprime la requête analyse croisée
CurrentDb.CreateQueryDef "R110_Gen_Temp_AC1", Sql 'crée la nouvelle requête
DoCmd.OpenQuery "R110_Analyse_Nature" 'ouvre la requête |
Partager