Query_DateSpansInDateRange.mdb |
Post Reply |
Author | |
A.D. Tejpal
Microsoft MVP Joined: 30 Jun 2008 Status: Offline Points: 192 |
Post Options
Thanks(0)
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.
|
|
Sponsored Links | Want a good read? Try The Summer of His Life (available on Amazon!) |
Post Reply | |
Tweet
|
Forum Jump | Forum Permissions You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |