Re: [MS_AccessPros] How to find missing dates

Duane,

Thank you very much...I will try that...

Best regards

Ahmed

On Thu, Dec 27, 2012 at 8:53 PM, Duane Hookom <duanehookom@hotmail.com>wrote:

> **
>
>
> 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
>
>


[Non-text portions of this message have been removed]



------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/