Results 1 to 4 of 4
  1. #1
    lugnutmonkey is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jan 2013
    Posts
    42

    Unhappy Selecting Year to date

    I would like to design a querie that selects all records with in the past twelve months for use in a Year to date comparison report. This report will look at the previous 24 months and compare them to one another and calculate the % Change.



    I have tried using
    Code:
    SELECT [Phone Log].*
    FROM [Phone Log]
    WHERE ((([Phone Log].[Date/Time]) Between Year(Date()) And Year(Date())-1));
    But nothing is selected when I run this query.

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You need to use the Year function on your field too, so you are comparing "apples to apples, i.e.:
    Code:
    WHERE Year([Phone Log].[Date/Time]) Between (Year(Date())-1) And Year(Date());

  3. #3
    lugnutmonkey is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jan 2013
    Posts
    42
    That actualy makes alot of sense. Thanks for the code and the reasonable explination.

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Your welcome!

    The key to remember is that the Microsoft Programs (Excel and Access), store dates as numbers, specifically, the number of days since January 1, 1900.
    So today's date is actually stored as 41289 (if there was a time component, it is stored as a fractional component at the end of that value, i.e. today at 8:00 AM would look like 41289.3333). You can see this by changing the Format of a date field in Excel or Access to a numeric format.

    So if you were checking to see if today's date fell between 2013 and 2014, the way it was originally written would return this check:
    41289 between 2013 and 2014
    which is obviously False.

    However, by applying the Year function on today's date instead, it returns:
    2013 between 2013 and 2014
    which is True.

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

Similar Threads

  1. Replies: 4
    Last Post: 01-09-2013, 11:16 AM
  2. Replies: 4
    Last Post: 01-10-2012, 06:26 PM
  3. Selecting a str Date
    By benjimillard in forum Queries
    Replies: 1
    Last Post: 06-24-2011, 02:00 AM
  4. Year to date sum
    By jzacharias in forum Database Design
    Replies: 6
    Last Post: 09-10-2010, 10:38 AM
  5. Replies: 1
    Last Post: 12-09-2005, 10:29 PM

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