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
| Private Sub btnExcelStock_Click()
Dim qd As QueryDef
Dim SQL As String
SQL = "SELECT p.PartNumber, p.Designation, p.NFournisseur as Fournisseur,p.RefFournisseur,rc.BU, rc.QteBULocaux AS QteDansLocaux, rc.QteBUStock AS QteDansStock, rc.QteBUSite AS QteSurSite,Nz(rc.QteBULocaux,0)+Nz(rc.QteBUStock,0)+Nz(rc.QteBUSite,0) AS VolumeTotalBU, rd.DernierPrixU, rc.QteBULocaux*rd.DernierPrixU AS MontantLocaux, rc.QteBUStock*rd.DernierPrixU AS MontantStock,rc.QteBUSite*rd.DernierPrixU AS MontantSite,(Nz(rc.QteBULocaux,0)+Nz(rc.QteBUStock,0)+Nz(rc.QteBUSite,0))*rd.DernierPrixU AS MontantTotal FROM (ReqCalculStockEtLocauxBU AS rc INNER JOIN Produit AS p ON rc.NProduit=p.NProduit) INNER JOIN ReqDernierPxU AS rd ON p.NProduit=rd.NProduit WHERE p.NProduit <>0"
If CocheLieu2 Then
If ModifLieu2 = "Stock" Then
SQL = SQL & "And rc.QteBUStock > 0"
End If
If ModifLieu2 = "LocauxShaktiware" Then
SQL = SQL & "And rc.QteBULocaux >0"
End If
If ModifLieu2 = "SurSite" Then
SQL = SQL & "And rc.QteBUSite >0"
End If
End If
If CocheBU2 Then
SQL = SQL & "AND rc!BU ='" & ModifBU2 & "'"
End If
SQL = SQL & ";"
Set qd = CurrentDb.CreateQueryDef("Requete_Temporaire", SQL)
DoCmd.OutputTo acOutputQuery, "Requete_Temporaire", acSpreadsheetTypeExcel9, "EtatStock.xls", True
DoCmd.DeleteObject acQuery, "Requete_Temporaire"
End Sub |
Partager