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

    Making sure records from previous years include weekends

    Hi there,

    I'm currently setting up a database to automatically send emails to customers on the anniversary of a purchase.

    Currently, the follow up date is populated on entry to the database by the criteria:

    =DateAdd('m',+12,Date())

    Thinking about this logically, the weekend that has just passed i.e 21/05/2016 - 22/05/2016 could have been weekdays in the previous year

    and therefore the query that looks for last year's orders will not find them as the query criteria is as follows

    DateAdd("yyyy",-1,Date())
    DateAdd("yyyy",-2,Date())
    DateAdd("yyyy",-3,Date())

    and so on.

    If there is a way on a monday, to include the two prior dates as well as monday's date

    the dates of orders that would be returned instead of:

    23/05/2015
    23/05/2014
    23/05/2013 .... etc

    would instead return the dates

    21/05/2015
    22/05/2015
    23/05/2015


    21/05/2014
    22/05/2014
    23/05/2014
    21/05/2013
    22/05/2013
    23/05/2013

    ..... etc

    Please ask for clarification if any of this seemed unclear, i'm new to posting on forums

    Many Thanks

    Joe

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    look at using the weekday function to identify is today is a Monday

    https://msdn.microsoft.com/en-us/lib...(v=vs.90).aspx

    you will also need to modify your criteria

    between dateadd("d",iif(Weekday(Date())=2,-2,0),DateAdd("yyyy",-1,Date())) and DateAdd("yyyy",-1,Date())

  3. #3
    jmwebster91 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Posts
    13
    That's perfect,

    thank you

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

Similar Threads

  1. Querying this DD/MM in all previous years
    By jmwebster91 in forum Access
    Replies: 9
    Last Post: 05-23-2016, 04:52 AM
  2. How to include blank records in reports
    By saudkazia in forum Reports
    Replies: 14
    Last Post: 10-12-2015, 05:06 PM
  3. Replies: 12
    Last Post: 08-10-2014, 12:54 PM
  4. Replies: 4
    Last Post: 03-29-2014, 01:29 AM
  5. Include Multiple Records in Email
    By gopherking in forum Programming
    Replies: 3
    Last Post: 09-21-2011, 09:29 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