Optional Variant Parameter Help (1 Viewer)

ChrisMore

Member
Local time
Today, 09:24
Joined
Jan 28, 2020
Messages
174
Hi everyone,

I am using Allen Browne's Inventory Control Quantity on Hand code and it has an optional vAsOfDate variant, as shown in the code below. I would like to use this to show the quantity on hand for a specific date but I can't get it working. I am currently using the code based on the most recent stocktake date and it works beautifully, however I would like to expand on this to be able to compare quantity on hand levels at a specific date to the latest stocktake quantity on hand level and then find the difference. How can I achieve this?

Code:
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.

I can post the code in full if you need it but it's quite long so thought I'd just include the first few lines showing the optional variant for now.

Thanks for your help.
Chris
 
Last edited:

Isaac

Lifelong Learner
Local time
Today, 02:24
Joined
Mar 14, 2017
Messages
8,738
What kind of problem are you currently experiencing?
I think posting the code and 'where' you are having the problem will be necessary to give the most meaningful input.
 

ChrisMore

Member
Local time
Today, 09:24
Joined
Jan 28, 2020
Messages
174
What kind of problem are you currently experiencing?
I think posting the code and 'where' you are having the problem will be necessary to give the most meaningful input.

Hi Isaac,

It's not so much that I've got a problem with the code it's more of I don't understand how to get it to work. I would like to use the code's optional vAsOfDate variant and enter a specific date as a parameter to get the quantity on hand levels as of that date. As I said in my first post, I would like to then compare these levels with the quantity on hand levels I am currently getting which is based on the most recent stocktake date.

Code:
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 Long          'Quantity at last stocktake.
    Dim lngQtyAcq As Long           'Quantity acquired since stocktake.
    Dim lngQtyUsed As Long          'Quantity used since stocktake.

    If Not IsNull(vProductID) 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, "dd\/mm\/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.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 Customer_Orders INNER JOIN Customer_Orders_Items ON " & _
            "Customer_Orders.Order_Number = Customer_Orders_Items.Order_Number " & _
            "WHERE ((Customer_Orders_Items.Product_Code = " & lngProduct & ")"
        If Len(strDateClause) = 0 Then
            strSQL = strSQL & ");"
        Else
            strSQL = strSQL & " AND (Customer_Orders.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

I’m here to help
Staff member
Local time
Today, 02:24
Joined
Oct 29, 2018
Messages
21,357
Hi Chris. Sounds like you want two values: the stock on hand at a specific date, and a stock on hand at the current level. Is that correct? If so, you may have to run the function twice: one for each date.
 

ChrisMore

Member
Local time
Today, 09:24
Joined
Jan 28, 2020
Messages
174
Hi Chris. Sounds like you want two values: the stock on hand at a specific date, and a stock on hand at the current level. Is that correct? If so, you may have to run the function twice: one for each date.

That's correct, although I already have the code working for the stock on hand at the current level. So my question is: how to I get it working for the stock on hand at a specific date?

Thanks,
Chris
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:24
Joined
Oct 29, 2018
Messages
21,357
That's correct, although I already have the code working for the stock on hand at the current level. So my question is: how to I get it working for the stock on hand at a specific date?

Thanks,
Chris
Have you tried passing the date you want to the optional argument?
 

Isaac

Lifelong Learner
Local time
Today, 02:24
Joined
Mar 14, 2017
Messages
8,738
Sounds like you need to create a second function, and why make the input date optional at all? Seems like it is required to do the comparison.
You could do like dbGuy said, or you could create one master function that requires BOTH input dates (current on hand and on hand as of date), and returns some meaningful result, I don't know "what", because I don't know precisely what you mean by "compare" (i.e. do you want the numerical difference between on hand as of previous date and current on hand? then it might return a Long. If you want a string result with both numbers, it might return #,# or something).

Instead of this function, I might also recommend trying to create a query. Then your code might just return a Dcount or Dsum from the query.
 

ChrisMore

Member
Local time
Today, 09:24
Joined
Jan 28, 2020
Messages
174
Have you tried passing the date you want to the optional argument?

The date will vary so that's why I would like it entered in a parameter. I have been trying to figure out how to do this but to no success. How do I create a parameter which passes the date entered to the optional argument?

Thanks
 

Isaac

Lifelong Learner
Local time
Today, 02:24
Joined
Mar 14, 2017
Messages
8,738
How do I create a parameter which passes the date entered to the optional argument?
Code:
Function SomeName(Optional dtDateAsOf as Date, ...........)

End Function
Not sure why you're making it optional if it's required for the calculation.
 

ChrisMore

Member
Local time
Today, 09:24
Joined
Jan 28, 2020
Messages
174
Sounds like you need to create a second function, and why make the input date optional at all? Seems like it is required to do the comparison.
You could do like dbGuy said, or you could create one master function that requires BOTH input dates (current on hand and on hand as of date), and returns some meaningful result, I don't know "what", because I don't know precisely what you mean by "compare" (i.e. do you want the numerical difference between on hand as of previous date and current on hand? then it might return a Long. If you want a string result with both numbers, it might return #,# or something).

Instead of this function, I might also recommend trying to create a query. Then your code might just return a Dcount or Dsum from the query.

That's right, I would like the numerical difference between on hand as of previous date and current on hand.
 

ChrisMore

Member
Local time
Today, 09:24
Joined
Jan 28, 2020
Messages
174
Code:
Function SomeName(Optional dtDateAsOf as Date, ...........)

End Function
Not sure why you're making it optional if it's required for the calculation.

If it's a new function it doesn't have to be optional, I was just thinking of using the optional variable on the original code to get both on hand values from the same function
 

Isaac

Lifelong Learner
Local time
Today, 02:24
Joined
Mar 14, 2017
Messages
8,738
I guess if you are struggling to write the code, this approach might be the best - If our only consideration is to achieve the objective with minimal effort (not commenting on efficiency at the moment)

- Leave the existing function the way it is.
- Write a new function to get your "difference" between as of dates, something like:
Code:
Function OnHandDifference(dtPreviousDate as date)

dim lngOnHandNow as long, lngOnHandPreviously as long
lngOnHandNow=OnHand(12, date())  'this returns today's on-hand
lngOnHandPreviously=OnHand(12,#5/1/2020#) 'this returns previous date's onhand
OnHandDifference = lngOnHandNow-lngOnHandPreviously

End Function

In this example, I have pretended that the product ID is 12 and 5/1 is the previous date you were interested in..
I have also assumed that a particular subtraction needs to be made, but you might want it in the reverse, up to you.
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:24
Joined
Sep 21, 2011
Messages
14,037
That's right, I would like the numerical difference between on hand as of previous date and current on hand.
PMFJI, but that is what it appears to do? You have to have a previous stockdate ?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:24
Joined
Oct 29, 2018
Messages
21,357
The date will vary so that's why I would like it entered in a parameter. I have been trying to figure out how to do this but to no success. How do I create a parameter which passes the date entered to the optional argument?

Thanks
Hi. Did you want the user to enter a date or pick from a calendar?
 

ChrisMore

Member
Local time
Today, 09:24
Joined
Jan 28, 2020
Messages
174
I guess if you are struggling to write the code, this approach might be the best - If our only consideration is to achieve the objective with minimal effort (not commenting on efficiency at the moment)

- Leave the existing function the way it is.
- Write a new function to get your "difference" between as of dates, something like:
Code:
Function OnHandDifference(dtPreviousDate as date)

dim lngOnHandNow as long, lngOnHandPreviously as long
lngOnHandNow=OnHand(12, date())  'this returns today's on-hand
lngOnHandPreviously=OnHand(12,#5/1/2020#) 'this returns previous date's onhand
OnHandDifference = lngOnHandNow-lngOnHandPreviously

End Function

In this example, I have pretended that the product ID is 12 and 5/1 is the previous date you were interested in..
I have also assumed that a particular subtraction needs to be made, but you might want it in the reverse, up to you.

Thanks, I shall take a look at it and try to get it working.
 

Isaac

Lifelong Learner
Local time
Today, 02:24
Joined
Mar 14, 2017
Messages
8,738
Thanks, I shall take a look at it and try to get it working.
Sounds good.
Note - in writing my suggestion I assumed what you mentioned which is the original code is working perfectly to find the on-hand as of whatever date is passed in. So hopefully just running it twice with different parameter values will do the trick.
 

ChrisMore

Member
Local time
Today, 09:24
Joined
Jan 28, 2020
Messages
174
PMFJI, but that is what it appears to do? You have to have a previous stockdate ?

That's right, but I would like to use it to show historic values using an older stocktake and the current on hand using the current stocktake
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:24
Joined
Oct 29, 2018
Messages
21,357
That's right, but I would like to use it to show historic values using an older stocktake and the current on hand using the current stocktake
Hi. Will the user enter arbitrary dates, or do you already have a table of dates you want to evaluate?
 

Users who are viewing this thread

Top Bottom