Results 1 to 4 of 4
  1. #1
    Flafel is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    3

    Query a multiple dates from a previous year

    Good day,

    I am a novice to SQL, but learning...

    I have an issue with a table, where there are a series of incorrect dates for 2017 that were added to the table that are the same month/day for 2016. I need to isolate those identical month/days for 2017. I have looked into both the DateSerial() command and the Day() command, without any luck retrieving multiple records and only able to retrieve a single day.

    Example of issue for the table:
    RecordId#, OpenDate
    1, 9/15/2016
    2, 9/15/2017 (this should be 9/14/2017)
    3, 9/13/2018



    Again, having the issue of retrieving the "all records" with the same month and day of the previous year, not just a single date as shown in the example. Dumb question, am I using the correct the syntax, WHERE Year(2016) and Day([OpenDate]) = Year(2017) and Day([OpenDate])....or syntax, WHERE DateSerial (2017,[OpenDate],9) = DateSerial(2016,[OpenDate],9)? However, neither statements are working? My apologies, if I am not providing enough information to help. Thank you in advance for your help.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Suspect not enough information provided but you probably need to use two queries

    1. create a query called allDates
    Code:
    SELECT dateadd("y",1,openDate) as NextYear
    FROM myTable
    WHERE Year(openDate)=2016
    2. create another query based on allDates
    Code:
    SELECT myTable.*
    FROM myTable INNER JOIN allDates ON myTable.openDate= allDates.NextYear
    WHERE year(openDate)=2017
    This should produce a list of all records in 2017 that have the same month/day as 2016

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Or create a calculated field in the query to get you the previous year\ same day and month:

    PrevYear: DateSerial(Year([OpenDate])-1,Month([OpenDate]),Day([OpenDate]))

    Now use this field in your join.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    Flafel is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    3
    Thank you to you both Vlad and Ajax!
    Appreciate the assistance and those suggestions got me where I needed to be.

    Take care

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

Similar Threads

  1. Replies: 16
    Last Post: 02-07-2020, 11:42 AM
  2. Replies: 8
    Last Post: 08-20-2019, 12:30 AM
  3. Replies: 3
    Last Post: 02-11-2014, 11:42 AM
  4. Previous Year Data Query Expression
    By NotReese in forum Queries
    Replies: 4
    Last Post: 11-22-2013, 04:54 PM
  5. Query Help - calculate variance previous year
    By brtucker in forum Queries
    Replies: 1
    Last Post: 01-31-2013, 05:40 PM

Tags for this Thread

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