Extracting a Specific Date Range

[alderking]

Hi Guys,

I have a table setup which basically details the arrival dates of stored items and the departure date.

I'm trying to create a query which will basically list how many items were stored at a specific month.

Any help would be greatly appreciated.

many thanks,

Matt

 

[Dhamdard]

In design view of query, under the arrival date of items, type below expression in criteria

Between #01-Nov-11# And #30-Nov-11#

You can provide a lot more criteria based on what you want. Search for more criteria on Access Help.

 

[alderking]

Thanks, although that wont work for me (probably because my explanation was poor)

Your query would just show the number of items that arrived in November. What if an item arrived in september and left in december? It would still be present in the warehouse during November.

i hope this makes sense.

 

[Dhamdard]

It means all items that don't have departure dates are in the store from the date of arrival. For Nov, your total items stored would be all entries where departure date is null.

For Oct, your total items stored will be any item arrived before Nov 1 where the departure date is null or the departure date is smaller than Nov 1.

If this make sense, you could do it as below:

For Current Stock:

SELECT Table1.Itemname, Table1.ArrivalDate, Table1.DepartureDate
FROM Table1
WHERE (((Table1.DepartureDate) Is Null));

Previous Month (Oct) Stock:

SELECT Table1.Itemname, Table1.ArrivalDate, Table1.DepartureDate
FROM Table1
WHERE (((Table1.ArrivalDate)<#11/1/2011#) AND ((Table1.DepartureDate) Is Null Or (Table1.DepartureDate)>#10/30/2011#));
[/code]


This is an example, you can set it to your needs if this is helpful.