Calendar Panel (Worksheet)

The Calendar panel has quite a number of uses aside from the obvious one of providing a handy three, four, nine or twelve month calendar, depending on the configuration of the Worksheet. (Three months are visible when the calendar shares space with both grid and graph; nine when the calendar/grid or calendar/graph displays are active. Hiding the Worksheet footer lets another 1 or 3 months, respectively, become visible.)

The real strength of the Calendar is that it is interactive with both the grid and the graphs simultaneously. As easy as it might be to 1) sort by a date field, 2) highlight a set of rows (say those during a given week) then 3) click Graph Selected Columns, it seemed to us more natural to just point at a start and an end date. When graphs are being displayed, the regraphing should be automatic. Of course, if you don't agree you can clear the AutoRegraph check on the Calendar panel.

(Note: clicking on a date filters data down to that single date alone, so in order to use the point-and-click method for date ranges, hold down the Shift key while choosing start and end dates.)

Another benefit of the calendar display is that dates which actually have records associated with them are highlighted. Holding the cursor over any of these bold face dates will (after a second or two) display some balloon text giving the most salient features of the related record(s). This feature applies only to the records contained in the current grid, so that when date spans are greyed-in (start-date to end-date) , only the highlit dates within that grey area will have related (unfiltered) records.

This may all sound complicated when put into words, but we hope that it works in a clear and natural fashion. After all, calendars have been around for a while, and most people find them quite intuitive.

To reset the Calendar to the current date, double-click on any specific date. This choice is due to a quirk of the control. We only get the options of recognizing double-clicks on actual dates, or on the Calendar as a whole. If we pick the latter, the scroll-by-month buttons at top are included. That results in odd behavior whereby the faster you try to scroll into the past, the faster you keep getting reset back to the future! (Finally got to use that phrase in a sentence . . .)

A couple of points worth noting about the Access ELF Calendar is that it inter-operates with Microsoft Access's own filter controls. To release the date-range filter, click the Remove Filter icon on the Access Form View Toolbar, or use Records / Remove Filter/Sort. If the screen "jiggles" unsuitably when you move the cursor across the calendar, you may want to disable the balloon tip feature, by unchecking the Mouseover Data box. Up to three dates per record set can be represented on the Calendar pane. Choose the one to associate with the Calendar by selecting its radio button.

One of the most unusual ways the calendar can be used is to focus on the dates that have been used as "sum dates" by the natural language translator's interpretation of "period" questions. For instance, Northwind might be interested in breaking out their transaction levels by any given time period. "Show the count of orders for each 3 week period beginning March 17th 1997" is transformed into a complex SQL statement which credits the number of logged orders for the period to the preceding Sunday. This helps us see how the sales have grown from a total of 22 during the first 3 week period to 44 in the last. We can focus in on the trend over any shorter period of time by opening both the graph and calendar views simultaneously. Clicking on March 16, 1997 and May 18, 1997, we get a closer look at the flat sales growth for the period.

SELECT DISTINCTROW Orders.OrderID , Orders.OrderDate , Orders.ShipName FROM Orders WHERE Orders.OrderDate >= #03/17/1997# ; SELECT count ( [elfQ1].OrderID ) AS [count of OrderID] , elfPeriod ( [elfQ1].OrderDate , 3 , "ww" , "03/17/1997" ) AS [OrderDate] FROM [elfQ1] group by elfPeriod ( [elfQ1].OrderDate , 3 , "ww" , "03/17/1997" ) ;

Rather than clicking on the calendar to control the grid, it's possible to work in the other direction. The calendar always resets to show the earliest matching date when a recordset is changed, so we can take advantage of this to "walk" through the months that employees have been hired. To begin, we ask for the employees listed with the ones hired most recently first: "Show the employees sorted down by hire date" Now entering a 1 in the Restrict Rows box leaves only the most recently-hired worker (Dodsworth). The calendar synchronizes with this HireDate. Changing the entry to 2 filters out all but the two most recently-hired workers. The calendar backs up to include the hire date of the second worker listed, Callahan.