Beginning/Ending Inventory

[rjmora87]

Hello,

I've been developing Access databases for various work projects since 2016. Right now I am working on a database to simplify some daily inventory reports, part of which involves tracking "aged" daily inventory.

One way I managed to implement this is to adapt Allen Brown's "quantity on hand" code. It works 99% of the time, but that 1% of the time it doesn't work properly is the days where we do have 0-day aged inventory. My adaptation ends up pushing the 0-day down a day or two.

Function OnHandAged(ProgramId As Variant, RptDate As Variant, AgeDate As Variant) As Long
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim SQL As String
Dim CLSD As Long, Inv As Long, Remainder As Long
    Set DB = CurrentDb
    OnHandAged = 0
    ' determine total closures
    SQL = "SELECT SUM([SumOfPDS]) AS [CLSD] FROM qrySumClosures " & _
        "WHERE [RptDate] <= #" & RptDate & "# And [Program] = '" & ProgramId & "'"
    Set RS = DB.OpenRecordset(SQL)
    If RS.RecordCount > 0 Then
        CLSD = Nz(RS!CLSD, 0)
    End If
    RS.Close
    ' calculate inventory to date
    SQL = "SELECT Program, AgedDate, SUM([PDS]) AS [Sum] FROM AgedInv" & _
        " WHERE RptDate <= #" & RptDate & "# And Program = '" & ProgramId & "'" & _
        " GROUP BY AgedInv.Program, AgedInv.AgedDate"
    Set RS = DB.OpenRecordset(SQL)
    ' subtract inventory count (PDS)
    ' from the total closures for each aged date
    Do Until RS.EOF
        DoEvents
        If CLSD > 0 And CLSD <= RS!Sum Then
            Remainder = RS!Sum - CLSD
            CLSD = 0
        ElseIf CLSD = 0 Then
            Remainder = RS!Sum
        Else
            Remainder = Remainder - RS!Sum
            If Remainder < 0 Then Remainder = 0
            CLSD = CLSD - RS!Sum
        End If
        If AgeDate = RS!AgedDate Then
            OnHandAged = Remainder
            Exit Do
        End If
        RS.MoveNext
    Loop
    RS.Close
    Set RS = Nothing
    Set DB = Nothing
End Function

I'd like to either fix the VBA function, or develop a query to eliminate it.

Attached is an Excel file that represents my current tables (AgedInv for receipts, and a Closures table) and the desired result.

Any help is appreciated.

 

[dynamictiger]

Not sure if this has been considered...but maybe an alternative thought process is required.

After wrestling with code for various applications a few years ago I started down a different path, using tables and so on instead. In this case I am suggesting, in case it hasn't been considered, using temporary tables to hold inventory in or perhaps to store the changed items in. And then either a second table with the new numbers in and using a query to get the data desired, rather than code.

Just a suggestion that may save a lot of work.

 

[hernan]

Please change qryClosure to Closure for testing purposes

Sub llamar()
Call OnHandAged_Hernan(1, #6/19/2019#, #6/19/2019#)
End Sub


Function OnHandAged_Hernan(ProgramId As Variant, RptDate As Variant, AgeDate As Variant) As Long
Dim DB As Database
Dim RS As Recordset
Dim rs2 As Recordset
Dim SQL As String
Dim CLSD As Long, Inv As Long, Remainder As Long
    Set DB = CurrentDb
    OnHandAged_Hernan = 0
'    ' determine total closures
'    'First: The total closure was done, I do not understand why you perform a sum in SumOfPDS, in addition
'    'your data have unique items for evary date(day) in Closure data
'    SQL = "SELECT SUM([SumOfPDS]) AS [CLSD] FROM Closures " & _
'        "WHERE [RptDate] <= #" & RptDate & "# And [Program] = '" & ProgramId & "'"
'    Set RS = DB.OpenRecordset(SQL)
'    If RS.BOF And RS.EOF Then
'        CLSD = 0
'    Else
'        CLSD = RS!CLSD
'    End If
'
'    RS.Close
'    Set RS = Nothing
    ' calculate inventory to date
    'The same, seeing your data, I can not understand why do you need perform the sum of PDS field, tell me why please
    SQL = "SELECT Program, AgedDate, PDS, SUM([PDS]) AS [Sum] FROM AgedInv" & _
        " WHERE RptDate = #" & RptDate & "# And Program = '" & ProgramId & "'" & _
        " GROUP BY AgedInv.Program, PDS, AgedInv.AgedDate ORDER BY AgedDate ASC"
        Debug.Print SQL
    Set RS = DB.OpenRecordset(SQL)
    ' subtract inventory count (PDS)
    ' from the total closures for each aged date
    Do Until RS.EOF
        SQL = "SELECT SumOfPDS AS CLSD FROM Closures " & _
              "WHERE RptDate = #" & RS(1) & "# And Program = '" & ProgramId & "' " & _
              "Order By RptDate DESC"
              Debug.Print SQL
        Set rs2 = DB.OpenRecordset(SQL)
        If rs2.BOF And rs2.EOF Then
        CLSD = 0
        Else
        CLSD = rs2!CLSD
        End If
        Debug.Print "RptDate=" & RptDate & ", AgedDate(Date for  you)=" & RS(1) & ", AdditionalReceipts=" & RS(2) & ",  Closed(Produced for you)=" & CLSD & ", Remain Vol:" & RS(2) -  CLSD '& ", Remainder=" & Remainder
    RS.MoveNext
    Loop
    RS.Close
    Set RS = Nothing
    rs2.Close
    Set rs2 = Nothing
    Set DB = Nothing
End Function