Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2013

    Unanswered: Current Year & Month for Multiple Fields

    Hello and thank you for viewing.

    I have a database in MS Access 2010 that keeps track of suppliers for my company along with their certificates. I need to create a query that will look at the certificates expiration date and tell me which ones are expiring *this* month. I can get a query to run based on one certificate by adding two expressions into the query of Year ([FAR145]) and Month ([FAR 145]).

    What I run into is if I try to look at more than one certificate. I have a total of seven certificates I need to pull. Even if I put this expression in the criteria for each certificate, the query comes up blank when there should be results.

    What I would like the query to do is pull the supplier name, ID, each certificate only if it is expiring in this month for this year. Can anyone help me get around this?

    Thank you again for viewing this.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    depends how you limit the rows you want returned
    use a suitable datetime function for Access, which probably means dateadd
    simplest would be to return all certificates that are expired and those that will expire in the next 28 days.

    assuming that you have stored an expiry date

    SELECT my,column,list FROM mytable
    WHERE expirydate <= DATEADD("d",28, DATE())
    replace the lower case items with the relevant names from your db

    this returns all rows whose expiry date is 28 days or less in the future

    if you must have precisely up to the next month then you are going to have to do a bit more work
    SELECT my,column,list FROM mytable
    WHERE expirydate < DATEADD("M",1,CDATE(YEAR(DATE()) & "/" & MONTH(DATE()) & "/01"))
    this sets the upper limit to be less than 1 months in the future so it will return all rows whose expirydate is less than (for today) 01/08/2013. the advantage of this technique is that it will always work even when the year flips.

    DATEADD youve seen before
    CDATE converts a date literal into a datevalue, and in this case we are finding the 1st of the month based on the current system DATE. for code portability I've specified the date in ISO format YYYY/MM/DD which means using the YEAR function, then the MONTH function, then adding 01. so for today the CDATE would result in "2013/07/01". adding one month through the DATEADD would return "2013/08/01" and you request all expirydates less than "2013/08/01"

    because you don't know if there is an expiry date you should probably allow for NULL expirydates

    SELECT my,column,list FROM mytable
    WHERE ISNULL(expirydate) OR expirydate < DATEADD("M",1,CDATE(YEAR(DATE()) & "/" & MONTH(DATE()) & "/01"))
    if you have stored lastexpiry + interval then youneed to work a bit harder on expirydate
    Last edited by healdem; 07-03-13 at 15:11.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts