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
| Function Compteur1(Largeur, Epaisseur, Longueur, Picking)
Dim Cpte As DAO.Recordset
Dim SQL As String
On Error Resume Next
SQL = "SELECT Count(*) AS Cpte" _
& " FROM (SELECT [Stock BNCE].Us, [Stock BNCE].Larg, [Stock BNCE].Ep, [Stock BNCE].Long," _
& " Sum(CLng([Q1])+CLng([Q2])+CLng([Q3])+CLng([Q4])+CLng([Q5])+CLng([Q6])+CLng([Q7])+CLng([Q8])+CLng([Q9])+CLng([Q10])+CLng([Q11])+CLng([Q12])) AS ST," _
& " [ST]*[larg]*[ep]*[long]/1000000000 AS [M3 stk]" _
& " From [Stock BNCE]" _
& " GROUP BY [Stock BNCE].Us, [Stock BNCE].Larg, [Stock BNCE].Ep, [Stock BNCE].Long, [Stock BNCE].Larg, [Stock BNCE].Long" _
& " UNION" _
& " SELECT [Stock BNCE (prod)].Us, [Stock BNCE (prod)].Larg, [Stock BNCE (prod)].Ep, [Stock BNCE (prod)].Long," _
& " Sum(CLng([q1])+CLng([q2])+CLng([q3])+CLng([q4])+CLng([q5])) AS QP°, [QP°]*[larg]*[ep]*[long]/1000000000 AS [M3 P°]" _
& " From [Stock BNCE (prod)]" _
& " WHERE ((([Stock BNCE (prod)].Date) > ([Formulaires]![Menu montage]![1er jr] - 1) And [Stock BNCE (prod)].Date < [Formulaires]![Menu montage]![dern jour] + 1))" _
& " GROUP BY [Stock BNCE (prod)].Us, [Stock BNCE (prod)].Larg, [Stock BNCE (prod)].Ep, [Stock BNCE (prod)].Long" _
& " UNION" _
& " SELECT [Stock BNCE (prépa)].Us, [Stock BNCE (prépa)].Larg, [Stock BNCE (prépa)].Ep, [Stock BNCE (prépa)].Long, Sum([Stock BNCE (prépa)].Q1) AS Qpp," _
& " [Qpp]*[larg]*[ep]*[long]/1000000000 AS [M3 Pp]" _
& " From [Stock BNCE (prépa)]" _
& " GROUP BY [Stock BNCE (prépa)].Us, [Stock BNCE (prépa)].Ep, [Stock BNCE (prépa)].Larg, [Stock BNCE (prépa)].Long) AS Tmp" _
& " WHERE (([Larg]+1000) & ([Ep]+1000) & ([Long]+10000) & [Us])<='" & (([Largeur] + 1000) & ([Epaisseur] + 1000) & ([Longueur] + 10000) & Picking) & "';"
Debug.Print SQL
Set Cpte = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
Compteur1 = Cpte.Fields(0)
Cpte.Close
Set Cpte = Nothing
End Function |
Partager