Optional Variant Parameter Help (1 Viewer)

ChrisMore

Member
Local time
Today, 23:29
Joined
Jan 28, 2020
Messages
174
And how did you want to see the results? On a form, in a query, a message box, etc.?

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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:29
Joined
Oct 29, 2018
Messages
21,358
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
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?
 

ChrisMore

Member
Local time
Today, 23:29
Joined
Jan 28, 2020
Messages
174
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?
Prompting the user for dates is fine.
Yes, just a simple calculation.
Thanks for your help.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:29
Joined
Oct 29, 2018
Messages
21,358
Prompting the user for dates is fine.
Yes, just a simple calculation.
Thanks for your help.
Okay, in that case, I would recommend you try the following.

1. Modify your query for your report to add the two dates you want to prompt the user. For example, let's say you have the following query:
SQL:
SELECT * FROM InventoryTable

Then, you can try to modify it like this:
SQL:
SELECT InventoryTable.*, [Enter Date1], [Enter Date2] FROM InventoryTable

2. In your report, you can add an unbound Textbox with the following Control Source:
Code:
=OnHand([ProductCode],[Enter Date2])-OnHand([ProductCode],[Enter Date1]

You didn't show us exactly how you were using the function, so I just took a guess, but you'll have to provide the correct Product Code.

Hope that helps...
 

ChrisMore

Member
Local time
Today, 23:29
Joined
Jan 28, 2020
Messages
174
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
 

CJ_London

Super Moderator
Staff member
Local time
Today, 23:29
Joined
Feb 19, 2013
Messages
16,553
Think the issue is no one can see the problem you are having because all we have is the function you are trying to use. Suggest provide some example data, show the code you are actually using, the actual result you get and the result you would expect to get from that example data
 

ChrisMore

Member
Local time
Today, 23:29
Joined
Jan 28, 2020
Messages
174
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 value, I also want the quantity on hand as of an arbitrary date, i.e so it only includes transactions up to and including that arbitrary date. I would like to produce a report similar to the report named 'Inventory_Report' so it shows the product details and the inventory amount as per the chosen date. I have a custom category named 'Inventory' where all of the above can be found in the database.

I hope this helps to explain in more detail what I would like to achieve.

Thanks,
Chris
 

Attachments

  • Inventory Database.accdb
    3.4 MB · Views: 112

Isaac

Lifelong Learner
Local time
Today, 16:29
Joined
Mar 14, 2017
Messages
8,738
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)
 

ChrisMore

Member
Local time
Today, 23:29
Joined
Jan 28, 2020
Messages
174
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)

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 work?

Thanks,
Chris
 

Isaac

Lifelong Learner
Local time
Today, 16:29
Joined
Mar 14, 2017
Messages
8,738
I just ran it in the immediate window.
Code:
?onhand(1,#8/1/2020#)
[press Enter]
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:29
Joined
Oct 29, 2018
Messages
21,358
Hi Chris. If you're not familiar with the Immediate Window, try adding a Textbox to your form or report and enter the following in it.
Code:
=OnHand(1,#8/1/2020#)
Just a thought...
 

ChrisMore

Member
Local time
Today, 23:29
Joined
Jan 28, 2020
Messages
174
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:29
Joined
Oct 29, 2018
Messages
21,358
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
Hi Chris. Assuming you have the following query.
SQL:
SELECT ProductID, OnHand(ProductID) As OH FROM TableName
Then try it this way:
SQL:
SELECT ProductID, OnHand(ProductID,[Enter Date]) As OH FROM TableName
Hope that helps...
 

Isaac

Lifelong Learner
Local time
Today, 16:29
Joined
Mar 14, 2017
Messages
8,738
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
 

ChrisMore

Member
Local time
Today, 23:29
Joined
Jan 28, 2020
Messages
174
Hi Chris. Assuming you have the following query.
SQL:
SELECT ProductID, OnHand(ProductID) As OH FROM TableName
Then try it this way:
SQL:
SELECT ProductID, OnHand(ProductID,[Enter Date]) As OH FROM TableName
Hope that helps...

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 perfectly now with my report and includes the difference comparison I also wanted.

Code:
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 had to add a format for the date as the parameter was requiring me to enter a US date format to get the correct data but as I'm in the UK I need dd/mm/yyyy. When I added this format it didn't make any difference but then when I changed it to mm/dd/yyyy strangely it works as the UK format. I don't understand why that would work but it does...

Thanks again,
Chris
 

ChrisMore

Member
Local time
Today, 23:29
Joined
Jan 28, 2020
Messages
174
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

Thanks Isaac, that is a great suggestion. I will give it a try and see how it works in my database.

Thanks also for your help in resolving my issue.

Chris
 

Users who are viewing this thread

Top Bottom