Excel Forum

Code: Select all

Option Compare Database
Option Explicit

Public Function FIFO(idproduct As Date, Balance As Long) As Long
  Dim nProduct As Long, LastQuantity As Long
  nProduct = DMax("[Date]", "Test3", "[Opt]=" & idproduct)
  LastQuantity = DLookup("[QTY]", "Test3", "[Opt]=" & idproduct & " AND [Date]=" & nProduct)
  Do
    If Balance > LastQuantity Then
      Balance = Balance - LastQuantity
      nProduct = DMax("[Date]", "Test3", "[Opt]=" & idproduct & " AND [Date]<" & nProduct)
      LastQuantity = DLookup("[QTY]", "Test3", "[Opt]=" & idproduct & " AND [Date]=" & nProduct)
      ElseIf Balance < 0 Then
      Balance = 0
 End If
  Loop While Balance > 0
End Function