Filter Stock/Inventory Report by Date/Period (Week,Month,Quarter,Year)

[LaughingDev]

Hi,

I am working on a system which manages orders, purchases and the inventory for a small trading company, much like the northwind example.

The db currently has 6 tables: Products, Customers, Orders, OrderDetails, Purchases and PurchaseDetails.

There are three queries which are being used to keep track of the StockOnHand, one which sums the total QtyPurchased, anther for total QtySold and the third which uses the first two to calculate QtyAvailable.

I am currently working on the inventory/stock reports and would like to have them filtered by date, so that it would produce the stats for the last week, month, quarter and year. Ideally using one report and having it filtered from a Form from which users can select the desired period via a ComboBox.

I am having trouble getting this to work, what Ive tried is to use the OrderDate field from the Orders table in the QtySold query but then the results of the query cease to give the correct sum as it is being groupedby the OrderDate field. So I tried setting it to WHERE instead of GROUPBY and gave it the following criteria:
>= DateAdd("d",-[period],date())
where I intended to pass the value for [period] from a form on which users could select the desired period (wk, mnth, qtr, year). However I am unsure about how I would do this.

I think Im going about it completely wrong, and would thus appreciate any advice/instructions which could help me acheive this functionality.

Thanks in advance.

 

[HiTechCoach]

Hi,

I am working on a system which manages orders, purchases and the inventory for a small trading company, much like the northwind example.

The db currently has 6 tables: Products, Customers, Orders, OrderDetails, Purchases and PurchaseDetails.

There are three queries which are being used to keep track of the StockOnHand, one which sums the total QtyPurchased, anther for total QtySold and the third which uses the first two to calculate QtyAvailable.

I am currently working on the inventory/stock reports and would like to have them filtered by date, so that it would produce the stats for the last week, month, quarter and year. Ideally using one report and having it filtered from a Form from which users can select the desired period via a ComboBox.

I am having trouble getting this to work, what Ive tried is to use the OrderDate field from the Orders table in the QtySold query but then the results of the query cease to give the correct sum as it is being groupedby the OrderDate field. So I tried setting it to WHERE instead of GROUPBY and gave it the following criteria:
>= DateAdd("d",-[period],date())
where I intended to pass the value for [period] from a form on which users could select the desired period (wk, mnth, qtr, year). However I am unsure about how I would do this.

I think Im going about it completely wrong, and would thus appreciate any advice/instructions which could help me acheive this functionality.

Thanks in advance.

You will also need to pass the first parameter for the DateAdd() function.

My recommendation:
What I find better is to calculate the criteria on your form. This means you work perform the DateAdd) on your form and store the value in a control . This control can be used as the criteria in your query.

>= forms!myForm.txtMyDateControl

This may help: Report Dialog Examples

 

[LaughingDev]

Thanks HiTech,

That seems to have done the trick, it is now filtering correctly. Just one more thing I would like to have is the option to select "all" in the combobox which would remove the filter and show all stock numbers for all products regardless of transaction dates.

Would this be possible and if so how would I fit it into the method Ive used to filter the report i.e. dateadd control on form with reference in query.

Thanks again in advance.

 

[HiTechCoach]

A simple way is to set the date to simple like 1/1/1900.