Periodic Stock Level Report Question?

[niuginikiwi]

I have tables with relationships as follows:
tblAcq----<tblAcqDetails>----tblPacks---<tblOrderDetails>---tblOrders

tblAcq and tblAcqDetails process incoming packs and tblOrders and
tblOrderDetails process outgoing details. tblPacks holds the packs product
records.

I want to come up with a query that I will supply the following criteria
through a form to produce a report.
Criterias will be a Date Range which will filter AcqDate from tblAcq and
OrderDate from tblOrders. Another criteria will filter PackName in tblPacks.

So I get a report that looks something like this:

Between FromDate And ToDate
PackName UnitsIn UnitsOut UnitsOnHand
F47 50 20 30
B46 100 50 50

and so on...

I know what I want but I am finding it hard to get there.
Can someone help me please?

 

[Klatuu]

There are two ways to do this.
One is to use the Where argument of the OpenReport method to pass the
parameters. The Where argument looks exactly like an SQL WHERE clause
without the word WHERE. I usually use a string variable to build the
argument. Something like this:
(in this example, lngView is defined elsewhere in the module)
Dim strWhere As String
Dim strDocName As String

strDocName = "rptOrders"

strWhere = "BETWEEN #" & Me.txtFromDate & "# AND #" Me.txtToDate & "#"
DoCmd.OpenReport strDocName, lngView, , strWhere

The other way is to filter the report's record source query referencing the
controls on the form. In the Query Builder, you enter the criteria in the
criteria line of the query like this:

BETWEEN Forms!MyForm!txtFromDate AND Forms!MyForm!txtToDate

 

[Marshall Barton]

One is to use the Where argument of the OpenReport method to pass the
parameters. The Where argument looks exactly like an SQL WHERE clause
without the word WHERE. I usually use a string variable to build the
argument. Something like this:
(in this example, lngView is defined elsewhere in the module)
Dim strWhere As String
Dim strDocName As String

strDocName = "rptOrders"

strWhere = "BETWEEN #" & Me.txtFromDate & "# AND #" Me.txtToDate & "#"
DoCmd.OpenReport strDocName, lngView, , strWhere

That should probably be:

strWhere = "AcqDate BETWEEN #" & Me.txtFromDate & "# AND
#" Me.txtToDate & "#"

 

[niuginikiwi]

Hi Dave,

Thank you for your post to my question.
You are pointing me in the direction of how to filter by date range which in
no doubt will help. I also have some date range filtering in place which I
can refer to for ideas.

However, my struggle here is to get a query that will be the record source
of the report. I have table and relationships scenario like what MVP Allen
Browne has on his
site at: http://www.allenbrowne.com/AppInventory.html
I searched the net for ideas to create a query that I will be able to filter
to get what I want on a report which doesn't appear to be much help but some
ideas are pointing towards creating 2 select queries and combining them with
UNION ALL which I have done and I have stuck from there now... cannot get any
furthere.

If you like, I can post the query SQL here.

All I want to achieve here is an inventory movement report (details of whats
come in and details of whats goine out and on the bottom have a totol ins,
outs and onhand) for a given date range.

Any more ideas on my dilema?