Results 1 to 4 of 4
  1. #1
    bimcompu is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2013
    Posts
    7

    Yet another Month Year query question

    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.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Here is an example query that extracts month and day from a date.
    Attached Files Attached Files

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    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

  4. #4
    bimcompu is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2013
    Posts
    7
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query for current month and year onward
    By tylerg11 in forum Queries
    Replies: 1
    Last Post: 12-30-2013, 12:10 PM
  2. Current Month/Year Query
    By Roadbeer in forum Queries
    Replies: 3
    Last Post: 06-17-2013, 01:20 PM
  3. Replies: 4
    Last Post: 05-26-2013, 03:28 PM
  4. Month and Day only not Year to create query
    By ssalem in forum Queries
    Replies: 3
    Last Post: 02-28-2013, 02:37 PM
  5. Query / report per month and year
    By ripper in forum Queries
    Replies: 2
    Last Post: 10-24-2011, 07:51 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums