Results 1 to 10 of 10
  1. #1
    jmwebster91 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Posts
    13

    Querying this DD/MM in all previous years

    I have a table that has a field "Order Date" which is based on the date of entry to the database. The "Follow up Date" is then calculated 1 year on from this in the table.



    In the query I want to find all records that have this DD/MM from all previous years

    Currently the criteria of the query reads:

    Follow up date :
    Date()
    Date()-365
    Date()-731
    Date()-1097
    Date()-1463

    This does not work for every year due to leap years and such.
    I would like a simple query criteria that returns all records with the DD/MM of "follow up date" equal to today's date

    Kind Regards

    Joe

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    is the datadd function


    dateadd("yyyy",-1,Date())

    dateadd("yyyy",-2,Date())

    dateadd("yyyy",-3,Date())

  3. #3
    jmwebster91 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Posts
    13
    I'm not trying to add dates though,

    I'm just trying to find all previous dates in a table with the same DD/MM as today's date.

    If this dateadd function is correct, where would I put it? in the query criteria?

    Joe

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    dateadd is the right function to use - the first example deducts 1 year from today - there isn't a dateminus function

    you would put it in your query - but not clear from you description why you have a follow up date. I would expect either

    where followupdate=date()

    or

    where orderdate=dateadd("yyyy",-1,Date())

    or for a range of same days over years something slightly different

    where format(orderdate("mmdd")=format(date(),"mmdd")

  5. #5
    jmwebster91 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Posts
    13
    Right I see!!

    The follow up date in my database is related to a mail merge feature, that emails customers 1 year on from a purchase, to remind them that the part needs to be serviced

    Sorry I didn't give much context to the question.

    The first criteria that you posted : orderdate=dateadd("yyyy",-1,Date())
    does produce last year's orders of this current day. This picture shows that I can return prior year's data with the number change
    Click image for larger version. 

Name:	spares follow up code.PNG 
Views:	19 
Size:	9.8 KB 
ID:	24636
    However, the data I have needs to be able to search data further back than the amount of criteria rows I have, (at least 15 years back, ideally 20 years).

    I tried the second criteria you posted but it returns an error message

    Click image for larger version. 

Name:	undefined.PNG 
Views:	18 
Size:	9.0 KB 
ID:	24637

    thank you for your patience

    Joe

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I tried the second criteria you posted but it returns an error message
    remove the criteria from where you have it (in both versions). and for the first version put


    dateadd("yyyy",-1,Date())

    in the criteria for the order date (plus for -2,-3 etc in the rows below

    and for the second version, create a new column

    Expr1:format(orderdate,"mmdd")

    and in the criteria for this new column

    format(date(),"mmdd")

  7. #7
    jmwebster91 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Posts
    13
    Thanks for your help Ajax, you've been very helpful.

    The database is now working as I had initially intended.

    Many thanks

    Joe

  8. #8
    jmwebster91 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Posts
    13
    Quote Originally Posted by Ajax View Post
    remove the criteria from where you have it (in both versions). and for the first version put


    dateadd("yyyy",-1,Date())

    in the criteria for the order date (plus for -2,-3 etc in the rows below

    and for the second version, create a new column

    Expr1:format(orderdate,"mmdd")

    and in the criteria for this new column

    format(date(),"mmdd")
    Another Question,

    It's just dawned on me that if this query runs on a Monday, the prior weekend dates (i.e Sarurday 21/05, Sunday 22/05; could have been a weekday last year and therefore return an order in the query.)

    is there a way on a monday to pull the prior two weekend dates through on the query of prior year dates.

    Joe

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    it is possible - which version I provided did you use

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I see you have started a new thread - so go with the responses you get there

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

Similar Threads

  1. Not Been Visited in over two years
    By angie in forum Queries
    Replies: 6
    Last Post: 05-04-2015, 10:23 AM
  2. Replies: 1
    Last Post: 12-17-2013, 01:53 AM
  3. fiancial years
    By Compufreak in forum SQL Server
    Replies: 4
    Last Post: 05-10-2013, 12:12 AM
  4. Replies: 1
    Last Post: 03-02-2012, 11:09 AM
  5. Select changes in the years
    By acs_one in forum Queries
    Replies: 8
    Last Post: 11-27-2010, 05:26 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