Search results

Thanks very much for your help theDBguy, in the end it was a very simple solution as I thought it might be. It was difficult to explain exactly what I required help with so maybe next time I need help I should attach my database from the start. I used the below for my query and it's all working...

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

Going by what you have said there I am concluding that you are right, as that is what the total should be. The first stocktake data for Product 1 is 1413 and there wasn't any transactions before 8/1/20 so that value is right. I am doing it wrong as I get a value of 0. How did you get it to...

Hi CJ, I have attached my database to this post. I am currently using the code (named Quantity_On_Hand) with the query 'Quantity_On_Hand_All_Products_Query' and it returns the current quantity on hand for every product, just as I want it to. However, as well as the current quantity on hand...

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

Prompting the user for dates is fine. Yes, just a simple calculation. Thanks for your help.

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

Arbitrary dates :)

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

Either is fine to be honest. Thanks.

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

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

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

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

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

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

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

Thank you so much, jdraw, that has solved my problem. It looks to be a really simple solution as well. But, of course, it's only simple when you know. I can now progress with my project! Cheers, Chris

jdraw, I have attached the database. The 'Quantity_On_Hand_All_Products_Query' is the one with the custom function. Thanks for your help, Chris

The_Doc_Man, thank you for your explanation. jdraw, I have created a query for all product codes (called 'Products_Query) and changed your code so 'Products_Query' is the name of the recordset. I then created a query with a Product_Code field and a custom function with the code as the...