Roger's Access Library Homepage
Forum Home Forum Home > Other Download Libraries > MS Access MVP Libraries > Tejpal, A.D.
  New Posts New Posts RSS Feed - Query_DateSpansInDateRange.mdb
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

>
Want a good read? Try The Summer of His Life (available on Amazon!)

Query_DateSpansInDateRange.mdb

 Post Reply Post Reply
Author
Message
A.D. Tejpal View Drop Down
Microsoft MVP
Microsoft MVP


Joined: 30 Jun 2008
Status: Offline
Points: 192
Post Options Post Options   Thanks (0) Thanks(0)   Quote A.D. Tejpal Quote  Post ReplyReply Direct Link To This Post Topic: Query_DateSpansInDateRange.mdb
    Posted: 08 Jul 2008 at 10:13pm
   This sample db demonstrates monitoring of common intersections (number of 
days) between multiple instances of date spans (Leave availed by employees) visa-
vis the selected date range. Such an intersection takes place when (Start date of 
date span is less than end date of selected date range) And (End date of date span 
is greater than start date of selected date range). 
 
    Selection of date range (DtFrom and DtUpto) for which employee's leave status 
is to be viewed, is carried out via combo boxes on the main form. On opening the 
form, default values on these combo boxes cover the full date range reflected by 
available data. As per built in safeguard, the user can not have a value for DtFrom 
that is greater than DtUpTo. Similarly, it is not feasible to use a value for DtUpTo 
that is less than DtFrom.
 
    The subform at left displays the names of all employees whose leave periods 
overlap the date range (either partly or in full). Against each employee, the total 
number of days overlap is also shown. By user selection via an option group, this 
list can be sorted either as per employee name (asc) or total days overlap (desc), as 
desired. Current record is highlighted in special color, while the font for total days 
overlap is highlighted in dark red color.
 
    For the employee featuring in current record of above subform, details of 
individual spells of leave (StartDate / EndDate in each case) for that employee, get 
displayed in chronological order in an adjacent subform at right. Only such of the 
leave spells are shown as have a positive intersection with the selected date 
range. In each case, portion of leave period that actually falls in the selected date 
range is also shown, duly highlighted in color.

    Whenever the user alters the date range by making new selections in the combo 
boxes on the main form, the information displayed by the two subforms as 
mentioned above, gets updated simultaneously. 

    Report Generation is provided in three styles as follows:
    Style 1 - All Employees:  Sorted as per employee name.
    Style 2 - All Employees:  Sorted as per total leave days overlap (Desc).
    Style 3 - Current Employee Only.
 
    Leave Chart (Employee-wise) - An interesting feature has been incorporated in 
the above reports. For each employee, individual leave spells overlapping the 
selected date range are depicted as a series of colored horizontal strips 
superimposed on a background bar representing the selected date range.

    Note - A common report is used for all three styles listed above. Filtration and sort 
order are tailored at run time, to match the combination of options selected by the 
user. As added convenience, the caption and fore color of the label at top right 
corner of the report are set to distinct values to mark different styles.

Download Access 2K/XP/2K3/2K7: uploads/37/Query_DateSpansInDateRange.zip

--

Email: adtejpal@gmail.com
Back to Top
Sponsored Links
>
Want a good read? Try The Summer of His Life (available on Amazon!)

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.03
Copyright ©2001-2019 Web Wiz Ltd.