- Local time
- Yesterday, 20:38
- Joined
- Oct 29, 2018
- Messages
- 21,471
And how did you want to see the results? On a form, in a query, a message box, etc.?Arbitrary dates
And how did you want to see the results? On a form, in a query, a message box, etc.?Arbitrary dates
And how did you want to see the results? On a form, in a query, a message box, etc.?
Hmm, that would mean before you open the report, you'll have to prompt the user for the dates. I would recommend using an input form instead and have a button on that form to open the report. Also, it sounds like you want the comparison result to be a simple calculation, rather than a bound data from a table, correct?I would like the results to be displayed on a report with other fields like product details, the current on hand quantity and the difference value
Prompting the user for dates is fine.Hmm, that would mean before you open the report, you'll have to prompt the user for the dates. I would recommend using an input form instead and have a button on that form to open the report. Also, it sounds like you want the comparison result to be a simple calculation, rather than a bound data from a table, correct?
Okay, in that case, I would recommend you try the following.Prompting the user for dates is fine.
Yes, just a simple calculation.
Thanks for your help.
SELECT * FROM InventoryTable
SELECT InventoryTable.*, [Enter Date1], [Enter Date2] FROM InventoryTable
=OnHand([ProductCode],[Enter Date2])-OnHand([ProductCode],[Enter Date1]
When I use the onhand function for product id 1, with date 8/1/2020, it returns the number 1413.Thank you theDBguy and Isaac for your kind help but unfortunately I haven't been able to get your suggestions to work. They both return a zero so it seems it isn't calculating the quantity on hand as of the previous date, or I am doing it wrong...
Looking back at Allen Browne's original article https://www.everythingaccess.com/tutorials.asp?ID=Inventory-Control:-Quantity-on-Hand it says "This function returns the quantity on hand for any product, optionally at any date."
Am I misunderstanding what this means or can the code literally be used to return the quantity on hand for any chosen date i.e choose the date via a parameter? If that's correct how do I go about completing this option?
As I have previously said, the code works perfectly when not entering a date as it just works off of the most recent stocktake and all transactions that follow. However, I would like to see the quantity on hand for a date which precedes the most recent stocktake.
Thanks,
Chris
When I use the onhand function for product id 1, with date 8/1/2020, it returns the number 1413.
1) Is that incorrect?
2) If so, how can I prove that to myself - can you tell me what data you would examine in which tables in your db to prove it should be something else? (i.e. how are you concluding that it's wrong)
Hi Chris. Assuming you have the following query.Oh, okay, I was trying to run it in a query and was getting 0 but I can see it's working in the immediate window .
Ideally I would like the user to be able to choose an arbitrary date and for it to return values for all products at the same time. I have sometime like this working with my query 'Quantity_On_Hand_All_Products_Query' where the current quantity on hand values appear for all products but this time I would need a parameter to appear and for the user to enter a date (such as your example 8/1/2020) and then it returns the values for all products.
Thanks,
Chris
SELECT ProductID, OnHand(ProductID) As OH FROM TableName
SELECT ProductID, OnHand(ProductID,[Enter Date]) As OH FROM TableName
Hi Chris. Assuming you have the following query.
Then try it this way:SQL:SELECT ProductID, OnHand(ProductID) As OH FROM TableName
Hope that helps...SQL:SELECT ProductID, OnHand(ProductID,[Enter Date]) As OH FROM TableName
SELECT Products.Product_Code, OnHand([Product_Code]) AS [Current On Hand Quantity], OnHand([Product_Code],Format([Enter Date],"mm/dd/yyyy")) AS [On Hand Quantity As Of A Date], Nz([Current On Hand Quantity],0)-Nz([On Hand Quantity As Of A Date],0) AS Difference
FROM Products;
I think this might be a great time to suggest a trick that I do a lot in my databases ... it is very handy and has so many uses.
Create one query (Query1 let's call it), with your Quantity_On_Hand_All_Products_Query design and use a specific OnHand except instead of the date, put something generic like #1/1/2020#.
So let's say you create the query just like you want it, and have a column like OnHand: Onhand([ProductIDColumnName],#1/1/2020#)
(replace ProductIDColumnName with the column name of the ID that's appropriate to pass in).
Next, create another query with any valid SQL you want. For example: Select 'foo'
I'll call this Query2
Put a control on a form with a date type, so users can select a date. Put a button with this code:
Currentdb.Querydefs("Query2").sql = replace(Currentdb.Querydefs("Query1").sql,"1/1/2020",format(Me.DatePickerControlname.Value,"mm/dd/yyyy"))
...Then a line of code to do whatever you want with your newly minted Query2: Me.Requery if it's the same form's RecordSource, or DoCmd.Openquery or whatever you want to do.
The basic idea is to have your master query with the perfect design except wherever the user-driven variable is, use some placeholder text.
Use the Replace function to set the second query's .Sql property to the same as the first, except replace placeholder with real. Then utilize second query.
To me this is better than parameter queries with the ugly popup that you have no control over user's input, and much better than queries that directly reference form controls. It requires very little effort and has endless uses.
HTH