Yet another Month Year query question

[bimcompu]

My objective is to count all of the devices shipped by month and year. My data covers three years - 2011 - 2013. When I use Date: Format([Ship Out Date],"mm/yyyy") in my query it works. When I try to select Between [Start Date] and [End Date] it works but only by the month. It is giving me all of the records for even the years outside of the start and end dates requested.

Example:
Date Project Id Count

10/2013 CH 586
10/2013 NEWHIRE 1231
10/2013 RFC 115
11/2012 CH 630
11/2012 NEWHIRE 1296
11/2012 RFC 227
11/2013 CH 583
11/2013 NEWHIRE 971
11/2013 RFC 65
12/2011 CH 419
12/2011 NEWHIRE 518
12/2011 RFC 155
12/2012 CH 323
12/2012 NEWHIRE 1025
12/2012 RFC 200
12/2013 CH 552
12/2013 NEWHIRE 932
12/2013 RFC 130

My start date was 10/2013
My end date was 12/2013

Why am I getting years 2011 and 2012?

What can I do to get only the years requested.



You guys are the best and I have learned so much from this forum. Please continue doing what you do. I am sure that I am not the only lerker here that is learning to get the most from their databases.
Thanks again for your help.

[ItsMe]

Here is an example query that extracts month and day from a date.

[rpeare]

post your SQL, if you are doing a BETWEEN on a formatted field (basically mm/yyyy) you are trying to sort a between on a TEXT field, so even if you started with a date value access is treating the newly formatted date as a string rather than an actual date, what you will want to do is put a WHERE clause in your query based on the ship date and ask for a starting and ending date but leave the value that shows as the formatted value.

so right now you have something like

SELECT Format([Ship Out Date],"mm/yyyy") as SHIPDATE, ITEM, Count(ITEM) as ITEMCOUNT FROM TABLENAME GROUP BY Format([Ship Out Date],"mm/yyyy"), ITEM

but you need something more like

SELECT Format([Ship Out Date],"mm/yyyy") as SHIPDATE, ITEM, Count(ITEM) as ITEMCOUNT FROM TABLENAME WHERE [Ship Out Date] between [Enter start date] and [Enter end date] GROUP BY Format([Ship Out Date],"mm/yyyy"), ITEM

The WHERE clause may end up being HAVING depending on what else is going on in your query but you're using an aggregate query so in your TOTALS line just add the ship out date field, make sure the totals line says WHERE (you'll notice the 'show' checkbox becomes unchecked) then put the criteria of

between [Enter start date] and [Enter end date]

in the criteria

[bimcompu]

Again, You Guys are awesome.
Thank you for the explaination of what was happening. You also taught me the advantages of using SQL instead of the GUI. With a few alterations to the field names and such - That step worked exactly as I need.
Thank you