1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
| Public Function Affectation(VarIdEqp As Long, VarDate As Date) As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sSQL As String
sSQL = " SELECT X.* FROM (SELECT tblMov .*, tblMovDet.IdEqp FROM tblMov, tblMovDet WHERE tblMov.IdMov=tblMovDet.IdMov) AS X" _
& " INNER JOIN (SELECT B.IdEqp, Max(A.dtemov) AS maxDate FROM tblMov AS A INNER JOIN tblMovDet AS B" _
& " ON A.idmov = B.idmov GROUP BY B.IdEqp HAVING ((( Max(A.dtemov)<=#" & Format(VarDate, "dd/mm/yyyy") & "#)) AND (((B.ideqp=" & VarIdEqp & ")))) AS Y" _
& " ON (Y.IdEqp = X.IdEqp) AND (Y.maxDate=X.DteMov)"
Set db = Application.CurrentDb
Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)
Affectation= rs("IdSrvc")
rs.Close
db.Close
Set db = Nothing
Set rs = Nothing
End Function |
Partager