Results 1 to 10 of 10
  1. #1
    azhar2006's Avatar
    azhar2006 is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    528

    Those who were late for Attendees


    Hi friends
    I am trying to write a Query that needs to have a date range of 60 days before the current date all the way through the last day of the year. Getting stumped on how I would do 60 days before the current date?
    Thank you so much

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    i use a form with 2 text boxes, txtStartDate, and txtEndDate. All queries look at these for the range.

    your txtStartDate would be
    =DateAdd("d",-60,Date())

  3. #3
    azhar2006's Avatar
    azhar2006 is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    528
    Thank you very much ranman256 but I need from the beginning of the year to come to an end
    This code has been used but has not worked
    Code:
    =DateAdd("d";-60;Date()) And DateAdd("m";-2;Date())

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    60 days from today is Date()-60. What is your end date - today or the end of the year? The query would have Between Date()-60 And ???

  5. #5
    azhar2006's Avatar
    azhar2006 is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    528
    Is it possible to describe it as an example?

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Not sure what your question is. Post the SQL of your query and let us know which date is the "To" date.

  7. #7
    azhar2006's Avatar
    azhar2006 is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    528
    Code:
    SELECT Payments.PaymentID, Payments.WorkorderID, Payments.PaymentAmount, Payments.PaymentDate, Workorders.CustomerID
    FROM Workorders LEFT JOIN Payments ON Workorders.WorkorderID = Payments.WorkorderID
    WHERE (((Payments.PaymentDate) Between DateAdd("d",-30,Date()) And DateAdd("m",-12,Date())))
    ORDER BY Payments.PaymentDate;

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    This query is getting records for the past 12 months up until one month ago. The syntax looks right, but it is contradicting your previous posts. Are you trying to get the first of January?

  9. #9
    azhar2006's Avatar
    azhar2006 is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    528
    For example, there are students attending the school lab once a month of the year. I recorded them in the database and recorded the date of attendance. What I want is who are those who did not attend?
    Note I write before each student the date of attendance.
    An example of this is late payments.

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I was asked to take a look at this. I have two thoughts.

    First, when using Between, I would always put the lesser value first. It will often work either way, but I have seen it fail. In other words, I'd reverse yours.

    To your issue, it sounds like this query will return who did attend, and you want those who did not. If so, use the unmatched query wizard to compare this query to a table/query of all students. That should return those who did not attend.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Late binding for MSO file dialog
    By Gina Maylone in forum Access
    Replies: 1
    Last Post: 01-26-2017, 04:58 AM
  2. How do I achieve late binding on a Chart?
    By RocketMonkey in forum Forms
    Replies: 1
    Last Post: 02-12-2013, 02:11 PM
  3. Late Database Design - Problems
    By Yesideez in forum Access
    Replies: 2
    Last Post: 06-26-2011, 05:20 PM
  4. Using the date to determine if something was late.
    By lyrikkmashairi in forum Access
    Replies: 3
    Last Post: 11-02-2010, 10:59 AM
  5. Registration of Attendees
    By camende in forum Programming
    Replies: 1
    Last Post: 06-30-2010, 03:47 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