ACCESS FEED: RE: [MS_AccessPros] How to find missing dates

Kamis, 27 Desember 2012

RE: [MS_AccessPros] How to find missing dates

Ahmed,

You can easily create a query of all dates based on simple table [tblNum0_9] with a single field [Num] and values 0 - 9. To get a query of 1,000 dates beginning with the current date and going to 999 days into the future:

SELECT H![Num]*100+T![Num]*10+O![Num]+Date() AS TheDate
FROM tblNum0_9 H, tblNum0_9 AS T, tblNum0_9 AS O

I aliased the same table with H for hundreds, T for Tens, and O for Ones.

The results on 12/27/2012 (I expect yours will display like 27/12/2012)

TheDate
12/27/2012
12/28/2012
12/29/2012
12/30/2012
...
9/20/2015
9/21/2015
9/22/2015

You can change Date() to Date()-500 to create a window of 1,000 dates with the current date in the middle.

Duane Hookom MVP
MS Access

----------------------------------------
> From: ahmedhashim1@gmail.com
>
> Duane,
>
> Thanks for your reply....I guess that's the only way...Are there anything
> to fill a table with dates within the specific range...Like for e.g I can
> restrict user to first add all dates from 01/12/2012 and 31/12/2012 in
> separate Dates table (let's called it tblDates) and then do the data entry
> in the transaction table so that we can find the missing dates, So for
> doing this, can we add something through Queries or VBA code to let user
> just enter the date ranges and it will fill the Dates in tblDates?
>
> Ahmed
>
> On Thu, Dec 27, 2012 at 7:23 PM, Duane Hookom <duanehookom@hotmail.com>wrote:
>
> > **
> >
> >
> > Ahmed,
> >
> > I would first create a table or query with every possible date. You can
> > then use the Find Unmatched query wizard to determine the missing dates.
> >
> > Duane Hookom MVP
> > MS Access
> >
> > ----------------------------------------
> > > From: ahmedhashim1@gmail.com
> > >
> > > Hi to all,
> > >
> > > I've a database where data entry made in table for Salesman expense
> > recording.
> > >
> > > Table structure:
> > > TransID (Auto)
> > > Trans Date (Date Time)
> > > EmpID
> > > ProductID
> > > Area
> > >
> > > Data entry is made on date-to-date basis, I need a way to find if any
> > particular date entry is missing in table. Like for e.g Data entry is made
> > for following dates (Date format is DD/MM/YYYY):
> > >
> > > 03/12/2012
> > > 04/12/2012
> > > 06/12/2012
> > > 07/12/2012
> > > 08/12/2012
> > > 10/12/2012
> > > 12/12/2012
> > > 13/12/2012
> > > 14/12/2012
> > >
> > > In the example given following dates are missing:
> > > 05/12/2012
> > > 09/12/2012
> > > 11/12/2012
> > >
> > > How can we trace these missing dates? Any suggestions? Queries/VBA?
> > >
> > > I am using Ms.Access 2007.
> > >
> > > Thanks and best regards
> > >
> > > Ahmed