Results 1 to 6 of 6
  1. #1
    soldat452002 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    16

    Find gaps in Dates

    Hello ,

    I have some data that needs has two dates Date Effective and Date Expiration with a Producer ID (PK). I need a query to search the Producer ID and find date gaps between these two Date Fields (date Eff), (Date Exp).


    Can someone guide me how to do this in a query? I have like 90,000 Records. Thanks




    ProducerId Year DateEff DateExp
    0000300063 2007 1/1/2007 12/31/2007
    0000300063 2008 1/1/2008 12/31/2008
    0000300063 2009 1/1/2009 12/31/2009
    0000300063 2010 1/1/2010 12/31/2010
    0000300063 2011 1/1/2011 12/31/2011
    0000300063 2012 1/1/2012 12/31/2012
    0000300063 2013 1/1/2013 12/31/2013
    0000300063 2014 1/1/2014 12/31/2014
    0000300063 2015 9/22/2015 12/31/2015
    0000300063 2016 1/1/2016 1/1/2200
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    What do you mean 'date gaps'?
    all dates between the 2 dates?
    or
    the # days between the 2 dates?

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The dB you provided (Test DB.zip) does not have the fields "Date Effective", "Date Expiration" or "Producer ID" in the table. (BTW, shouldn't have spaces in object names)

  4. #4
    soldat452002 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    16
    There cannot be gaps between days so in the example there is a lapse between 1/1/2015 and 9/21/2015. I need to identify those to avoid lapses.

  5. #5
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Query, where the effDate plus 1 day <> Exp date....

    select * from table where [dateExp] <> dateAdd("d",1,[dateEff])

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

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

Similar Threads

  1. Replies: 4
    Last Post: 03-25-2015, 04:37 PM
  2. Find difference of values between dates
    By Miquel1 in forum Queries
    Replies: 8
    Last Post: 12-10-2013, 10:37 AM
  3. Replies: 1
    Last Post: 12-22-2012, 12:38 PM
  4. Replies: 3
    Last Post: 05-01-2012, 01:57 PM
  5. find out values between two dates column
    By learning_graccess in forum Queries
    Replies: 3
    Last Post: 04-20-2012, 04:17 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