Access: Making a Calendar

CALENDARMAKER TOOL

Figure 1 shows the menu that drives the CalendarMaker tool. You can also choose to show additional information from other queries designed for the calendar. For example, holiday names will come from a query called “qCalendarHolidays_US,” which appears in the upper-left of each relevant day cell.

You can add your own data for each day, too. That data will be printed in the middle of each day cell. In this example, our user, Abigail, tracks her appointments with the calendar, so she’s using a query called “qCalendarData_Appointments_Abigail,” with the title “Appointments for Abigail.” (Figure 2 shows a sample calendar with holidays and appointment data.)

Some of the features and settings you can use and/or adjust in the tool include:

Date. When the CalendarMaker opens, the date is set to today. Change the calendar date using the “Date in Start Month” textbox or choose from the Year and Month combo boxes.

PDF. You can create PDF files by ticking the “Output to PDF” checkbox. When you open the report, Access will instead generate a PDF file and open that.

First day of the week. You can select the week to begin on any day you want. In many countries, the standard first day is Monday. Or maybe you don’t work on Tuesdays and Wednesdays, so it would be nice to show weeks beginning with Thursday.

Number of months. Instead of just one month, you can change the number of months to however many you want.

Languages. The CalendarMaker uses the language you’re specified in the Windows Region settings. Your day and month names will be in your language.

Queries. The calendar can use other queries to show additional data for specific dates on the calendar. To get the queries to merge with the calendar, assign specific fieldnames that the calendar needs to columns in your query as calculated names (or field aliases).

HOLIDAY QUERY

There’s a table called “cal_HolidayCtry” with fields for year, month, day, country code, day name, and whether or not it’s an observed holiday. The holiday query for the calendar report needs the following field names: Text1 (text containing the day name), CalYr (four-digit year), CalMo (month number, 1-12), CalDa (day number, 1-31), and IsBold (true or false).

Here’s the SQL to line up data from the “cal_HolidayCtry” for U.S. holidays:

SELECT cal_HolidayCtry.DayName AS Text1

, CBool( IsObs ) AS IsBold

, cal_HolidayCtry.Yr AS CalYr

, cal_HolidayCtry.Mo AS CalMo

, cal_HolidayCtry.Da AS CalDa

, cal_HolidayCtry.Ctry

FROM cal_HolidayCtry

WHERE (( cal_HolidayCtry.DayName Is Not Null )

AND ( cal_HolidayCtry.Da Is Not Null )

AND ( cal_HolidayCtry.Ctry="US" ));

If you’re printing other data on the calendar, the font size for holiday names will be smaller.

DATA QUERY

The “qCalendarData_Appointments_Abigail” query contains the same fields as the holiday query, except IsBold isn’t required or used. This query uses three tables to track appointments, appointment types, and contact names. The appointments table has a field with the date and time of an appointment called “dtmAppt.” The “CIDfor” field is a long integer that stores the contact ID for the individual having the appointment—in this case, Abigail. And “CIDwith” is the contact ID to describe who Abigail is meeting with. If the person doesn’t have a full contact record, his or her name can go in the “ContactName” field. The contacts table has “NameOfficial” to correlate the ID to a name. And in the appointment types table, the “ApptTyp” field has values like “lunch,” “meeting,” “demo,” and “follow-up.”

Here’s the SQL:

SELECT Trim(IIf( TimeValue( dtmAppt )<>0

,Format( dtmAppt ,"h:nn"),"")

& (" "+ ApptTyp ) & (" - "+ NameOfficial ) & (" ("+ ContactName +")")) AS Text1

, Year( dtmAppt ) AS CalYr

, Month( dtmAppt ) AS CalMo

, Day( dtmAppt ) AS CalDa

, CIDfor

FROM sample_ApptType

LEFT JOIN (sample_Appointment

LEFT JOIN sample_Contact

ON sample_Appointment.CIDwith = sample_Contact.CID)

ON sample_ApptType.ApptTyID = sample_Appointment.ApptTyID

WHERE (( CIDfor=1 )

AND ( dtmAppt Is Not Null ));

Date is split into its parts using the Year, Month, and Day functions. When CIDfor is 1, the appointments are for Abigail.

Text1 is an expression for what will appear in each date cell of the calendar. An example of text printed on a day is “10:00 Meeting - Art Supplies (Jenny), 12:00 Lunch - Backseat Painting (Bob).” Contacts not in the contact table are written in parentheses. With more than one appointment, they’re looped and concatenated with your Windows list separator, so be careful you don’t write too much. There isn’t a check for that!

Download the CalendarMaker tool: SF2001CalendarMaker.

SF SAYS

Create monthly calendars, for weeks starting on any day, to help plan your schedule and be more productive.