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

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.
flaggkortAsked:
Who is Participating?
 
Mike EghtebasConnect With a Mentor Database and Application DeveloperCommented:
make table tDates. This table has one field called DateVal (date/time). For now, populatet it with one worth of the data like

2/22/2009
2/23/2009
2/24/2009
2/25/2009
2/26/2009
2/27/2009
2/28/2009
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.
Mike
0
 
Gustav BrockCIOCommented:
Splendid article here about finding missing values in a sequence - also for dates:

http://www.devx.com/dbzone/Article/40345/1954

/gustav
0
 
Jeffrey CoachmanMIS LiasonCommented:
flaggkort,

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:

JeffCoachman
Access-EEQ-24169649PreventMissin.mdb
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.