Results 1 to 6 of 6
  1. #1
    John Donovan is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2010
    Posts
    7

    Many criteria dates to enter to compare months of various years


    I have to send bereavement cards for anniversaries of death every three years for the month of bereavement. (eg any clients who died in the month of June 2014 or 2013 or 2012). Next month (July etc ) the same will recur. At present, I raise a query where I show the query Start Date 1/6/2014 End Date 30/6/2014, OR Start Date 1/6/2013 End Date 30/6/2013, OR Start Date 1/6/2012 End Date 30/6/2012. The same procedure is processed for the months of July, August etc etc. I wish to create the report based on the query where staff who know nothing about Access. There is an Access table of the names and dates of death. but only the dates of deaths. How can I make it easy for them to produce this report each month ? I know little about VBA, but a reasonable knowledge of Queries and SQL.
    Thanks in advance
    John D

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If cards are to be sent every 3 years and this is 2015, why would you send cards for deaths in 2014 and 2013?


    Build query with filter criteria that is calculated from the current year/month.

    Or

    Build query with dynamic parameter input prompts (I NEVER use dynamic parameter queries).

    Or

    Build a form with unbound controls where users can input filter criteria. Review http://www.allenbrowne.com/ser-62.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    John Donovan is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2010
    Posts
    7
    Sorry I did not explain myself well enough. My client assists in bereavement where a parent of a young child dies at an early age. The client maintains the child name, date of death and other matters in a Access Table. The policy of my client is that for the anniversary for the next three years after death, a bereavement card is sent with flowers etc. A report is required each month for anniversaries next month. eg The parents of any Child who died in June 2014, 2013 or 2012 will be sent a card and flowers. A report (based on a Query ?) will be printed late May for organising this June matter. Ditto for every month ...eg a report late June for July. What is the easy (!!!) way for the client (who has Access 2010, but no knowledge of entering complicated data) to obtain this report ? Is it a Query or a Form ? I feel it would be difficult for the client to use a series of OR statements in a Criteria.
    Hope this helps, Thanks
    John

  4. #4
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    use the date function and work back from there e.g.

    Code:
    SELECT *
    FROM myTable
    WHERE year(DoD)>year(dateadd("m",1,Date()))-3 AND month(DoD)=month(dateadd("m",1,Date()))

  5. #5
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    re form, query or report - depends on what he is going to do with the data, but on balance a form. If it is just a reference list from which cards will be handwritten then I would suggest a report he can print out, or perhaps a query to export to excel - either of these can be controlled by a button on a form rather than the user having to open the query or report. If the intention is that he will be able to print the cards and envelopes then I would suggest a form since he will need pauses in the process to load the printer with the appropriate stationery.

  6. #6
    John Donovan is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2010
    Posts
    7
    Thanks- it is so easy when you see the answer !!
    John D

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

Similar Threads

  1. Replies: 1
    Last Post: 02-12-2013, 02:33 AM
  2. Replies: 1
    Last Post: 07-10-2012, 06:23 AM
  3. Display Age in Years, Months, Days
    By jsimard in forum Programming
    Replies: 1
    Last Post: 01-18-2012, 08:08 PM
  4. Adding months to years in queries
    By TonyB in forum Queries
    Replies: 2
    Last Post: 07-29-2011, 09:29 AM
  5. Replies: 1
    Last Post: 06-09-2011, 09:15 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