Solved Modifying Allen Browne's Inventory Control to display decimal numbers

[ChrisMore]

Hi everyone,

I have been using Allen Browne's Inventory Control code for quite some time now and it has been working perfectly. However, there is now a requirement for one of the stocked product's quantity on hand to be displayed as a decimal number. I have changed the data types of the table fields to Single or Double and neither have worked. I have also modified the code as below to declare the Single data type (I also tried to declare Double) but the Quantity On Hand isn't displaying as a decimal and the number is simply rounded instead.

Am I missing something with the code or do I need to change something elsewhere to get this working?

Thanks,
Chris

Public Function onhand(vProduct_Code As Variant, Optional vAsOfDate As Variant) As Long
    'Purpose:   Return the quantity-on-hand for a product.
    'Arguments: vProduct_Code = the product to report on.
    '           vAsOfDate  = the date at which quantity is to be calculated.
    '                           If missing, all transactions are included.
    'Return:    Quantity on hand. Zero on error.
    Dim DB As DAO.Database          'CurrentDb()
    Dim rs As DAO.Recordset         'Various recordsets.
    Dim lngProduct As Long          'vProduct_Code as a long.
    Dim strAsOf As String           'vAsOfDate as a string.
    Dim strSTDateLast As String     'Last Stock Take Date as a string.
    Dim strDateClause As String     'Date clause to use in SQL statement.
    Dim strSQL As String            'SQL statement.
    Dim lngQtyLast As Single        'Quantity at last stocktake.
    Dim lngQtyAcq As Single         'Quantity acquired since stocktake.
    Dim lngQtyUsed As Single        'Quantity used since stocktake.

    If Not IsNull(vProduct_Code) Then
        'Initialize: Validate and convert parameters.
        Set DB = CurrentDb()
        lngProduct = vProduct_Code
        If IsDate(vAsOfDate) Then
            strAsOf = "#" & Format$(vAsOfDate, "dd\/mm\/yyyy") & "#"
        End If

        'Get the last stocktake date and quantity for this product.
        If Len(strAsOf) > 0 Then
            strDateClause = " AND (StockTake_Date <= " & strAsOf & ")"
        End If
        strSQL = "SELECT TOP 1 Stocktake_Date, Product_Quantity FROM Stocktake " & _
            "WHERE ((Product_Code = " & lngProduct & ")" & strDateClause & _
            ") ORDER BY Stocktake_Date DESC;"

        Set rs = DB.OpenRecordset(strSQL)
        With rs
            If .RecordCount > 0 Then
                strSTDateLast = "#" & Format$(!Stocktake_Date, "mm\/dd\/yyyy") & "#"
                lngQtyLast = Nz(!Product_Quantity, 0)
            End If
        End With
        rs.Close

        'Build the Date clause
        If Len(strSTDateLast) > 0 Then
            If Len(strAsOf) > 0 Then
                strDateClause = " Between " & strSTDateLast & " And " & strAsOf
            Else
                strDateClause = " >= " & strSTDateLast
            End If
        Else
            If Len(strAsOf) > 0 Then
                strDateClause = " <= " & strAsOf
            Else
                strDateClause = vbNullString
            End If
        End If

        'Get the quantity acquired since then.
        strSQL = "SELECT Sum(Purchase_Orders_Items.Amount_of_Goods_Received) AS Amount_of_Goods_Received " & _
            "FROM Purchase_Orders INNER JOIN Purchase_Orders_Items ON Purchase_Orders.Purchase_Order_Number = Purchase_Orders_Items.Purchase_Order_Number " & _
            "WHERE ((Purchase_Orders_Items.Product_Code = " & lngProduct & ")"
        If Len(strDateClause) = 0 Then
            strSQL = strSQL & ");"
        Else
            strSQL = strSQL & " AND (Purchase_Orders_Items.Date_Goods_Arrived " & strDateClause & "));"
        End If

        Set rs = DB.OpenRecordset(strSQL)
        If rs.RecordCount > 0 Then
            lngQtyAcq = Nz(rs!Amount_of_Goods_Received, 0)
        End If
        rs.Close

          'Get the quantity used since then.
        strSQL = "SELECT Sum(Customer_Orders_Items.Order_Quantity) AS Order_Quantity " & _
            "FROM Shipments INNER JOIN Customer_Orders_Items ON " & _
            "Shipments.Order_Shipment_Number = Customer_Orders_Items.Order_Shipment_Number " & _
            "WHERE ((Customer_Orders_Items.Product_Code = " & lngProduct & ")"
        If Len(strDateClause) = 0 Then
            strSQL = strSQL & ");"
        Else
            strSQL = strSQL & " AND (Shipments.Production_Complete_Date " & strDateClause & "));"
        End If

        Set rs = DB.OpenRecordset(strSQL)
        If rs.RecordCount > 0 Then
            lngQtyUsed = Nz(rs!Order_Quantity, 0)
        End If
        rs.Close

        'Assign the return value
        onhand = lngQtyLast + lngQtyAcq - lngQtyUsed
    End If

    Set rs = Nothing
    Set DB = Nothing
    Exit Function
End Function

 

[theDBguy]

Try changing it to this:

Public Function OnHand(vProduct_Code As Variant, Optional vAsOfDate As Variant) As Double

 

[ChrisMore]

Haha, I was missing something with the code - it was as simple as that!

Thanks for your help all the same

 

[theDBguy]

Haha, I was missing something with the code - it was as simple as that!

Thanks for your help all the same

Good luck with your project.

 

[Pat Hartman]

Just FYI, If you need no more than 4 decimal digits, it is best to use the Currency data type rather than single or double to avoid floating point errors. Currency is a data type as well as a format. You can define the type as Currency but still display it as fixed or pct or currency.

Luck Chung wrote an article a long time ago on this problem. You can find it at www.fmsinc.com. Search for "when Access math doesn't add up"