Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Timberville, VA
    Posts
    1,438
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Birthday cover sheet

    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!
    Attached Files Attached Files

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,496
    Thanks
    3
    Thanked 42 Times in 42 Posts
    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.
    Wendell

  4. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Timberville, VA
    Posts
    1,438
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Thank you, Wendell! That's enough to get started.

  5. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Timberville, VA
    Posts
    1,438
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Wendell, just curious about your comment about January birthdays. Why especially January?

  6. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,496
    Thanks
    3
    Thanked 42 Times in 42 Posts
    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.
    Wendell

  7. #6
    Lounger
    Join Date
    Feb 2011
    Posts
    43
    Thanks
    0
    Thanked 6 Times in 6 Posts
    Why not use a query with criteria
    Between Date()+1 AND DateSerial(Year(Date()), Month(Date())+2,0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •