Reporting and filtering dates in access 2007?

Question

I'm having a customer look up a report between FROM and TO dates. Basically for any month a client can request a report, what I need to do is show the QTY of units in the inventory as of the last day of previous month. So if client is looking to make a report for 12/23/2015 - 12/28/2016 then I need to show the QTY of units in inventory (beginning inventory) on 11/30/2015. I had someone recommend I use DateSerial, but it doesn't seem to do what I want it to do. Basically the FROM AND TO dates are values that I pass on from a Visual Basic application into an empty form and they get used in the report.

I'm not sure if I need to make changes to my queries. Here's what the report does..

With the report, I need to be able to show the client the following... ItemID

Beginning Inventory (as of last day of previous month)
Unit Price
Bought Units (for report date)
Sold Units (for report date)
Returned Units (for report date)
Subtotal (of bought +sold+returned)
Inventory on hand (beg + subtotal)

So what I do is in my first query I get the Unit Price, and Beginning Inventory (currently not filtering by last day of previous month).

Then I have 3 more queries, one for Bought, Sold and Returned where I link the ItemID's and I filter the TransactionDate (between dates form!formdates!txtFrom and form!formdates!txtTo).

Then I have the last query where I combine the first one with the other 3. Now I'm pretty sure I should include the DATESERIAL() function in the first query? And filter the TransactionDate (this is the column i need to look through to count beginning inv, as well as look for Bought, Sold, Returned Items).

Please help!

jpro1001 jpro1001 · Accepted Answer · 2016-01-06T18:27:10

The DateSerial() function should return the last day of the previous month. Here is an example query for your problem:

SELECT ID, myDate FROM Table1 WHERE (((myDate)=DateSerial(Year([enter start date:]),Month([enter start date:]),0))) OR (((myDate) Between [enter start date:] And [enter end date:]));

Documentation: https://msdn.microsoft.com/en-us/library/bbx05d0c(v=vs.90).aspx

Reporting and filtering dates in access 2007?

1 Answers