Birthday cover sheet

**Caesar3** · 2012-05-02 15:52

Our office administrative assistant keeps a roster of our people and their birthdays. As a birthday approaches, she prepares a greeting card for everyone to sign. She then clips a cover sheet to the card and circulates it around the office.

This cover sheet is just a Word document that lists everyone's name with a line next to it for each person to initial the cover sheet. Before she circulates the card and cover sheet, the admin assistant removes the name of the person who will receive the card. Then she hand-writes that name down the side of sheet.

It seems to me there's got to be a better way to do this, and I've turned to Access. The attached database shows what I'm trying to do. You'll see one table (tblPeople) that stores names and birthdays; a query; a form (to print the cover sheet); and a report (the cover sheet).

The form should let the user select the name of the birthday boy or girl, then click a button to print the cover sheet. The cover sheet will print with everybody's name in the list on the left side of the report (in portrait view) except the name of the person who's having the birthday. The selected name will print in a large, bold font in a text box somewhere on the sheet. (Perhaps in the page header ... ?)

I've built the basic parts. Can someone help me, please, with whatever it takes to make this thing work. Thank you!

**WendellB** · 2012-05-04 07:18

A couple of suggestions to get you started. On the People form you could set the data source for the form to only select people who have a birthday coming up in the next 15 or 30 days with a criteria that uses a DateSerial() function to compare the current date to each person's birthday in the current year (January birthdays may be a little tricky).

On the report, you probably want to modify the query that is the data source to exclude the person being viewed on the form. So the criteria on the ID column would be <> Forms!frmBirthdays!ID - you will need to add the ID to the form of course. Having done that you will want to use a separate data source for the report.

**Caesar3** · 2012-05-04 14:14

Thank you, Wendell! That's enough to get started.

**Caesar3** · 2012-05-04 22:14

Wendell, just curious about your comment about January birthdays. Why especially January?

**WendellB** · 2012-05-05 11:10

In order to show only the birthdays for the upcoming month, it is typical to add a certain number of days to the current month. And since you are need to build a date in Access, you will need to figure out the year of the next birthday coming up. If you are in December and trying to show birthdays in the next 30 days for example, then you need to check whether the birthday is in December - if so use the current year. Otherwise if it is in January, then you need to add one (1) to the current year. But if you are in January, then you use the current year. Hope that makes my comment a bit clearer.

**Cronk** · 2012-05-08 04:59

Why not use a query with criteria
Between Date()+1 AND DateSerial(Year(Date()), Month(Date())+2,0)

Thread: Birthday cover sheet

Thread Tools