How do I write a query in Access 2007 to display dates missing in sequence

Posted on 2009-02-23
Last Modified: 2012-05-06
I have a table that gets updated every night when a store runs a report.  Sometimes the stores forget and that row of the table will not have that dates information.  So I guess I need a formula for example if 2/1/09 -2/21/09 rows are updated but missing 2/5 and 2/8  I need for the report to show me the dates 2/5 and 2/8 as being missing.  I know how to put the date range in the criteria but I don't know a formula for checking for missing dates in the sequence.
Question by:flaggkort
    LVL 33

    Accepted Solution

    make table tDates. This table has one field called DateVal (date/time). For now, populatet it with one worth of the data like

    knowing that there is a missing data for an store. Later, this table will be automatically filed in. You don't have to do it manually.
    Then make a query with an Outer Join and critria for days in your data table set to:
    Is Null
    This will show the missing dates. This is just a test for you to see if you like it. For now, also add store id for a particular store. If you make a query of your data table and post its SQL here I can help you wuth the new query we discussed above.
    LVL 48

    Expert Comment

    by:Gustav Brock
    Splendid article here about finding missing values in a sequence - also for dates:

    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    You can also use some pretty simple things to prevent missing values in the first place.
    (Like checking for a missing value when you try to enter a new record)

    Try this simple, basic example:


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now