Results 1 to 5 of 5
  1. #1
    Join Date
    May 2006
    Posts
    25

    Red face Unanswered: Pull out a list of birthdays a week in advance

    Hi there,

    I have a Database that keeps track of our Participants/Members. I am trying to figure out a way to get the database to pull out a list of participants whose birthdays fall on the following week.

    So, at the beginning of each week I will get a report that tells me who will be having a birthday the following week.

    I have managed to create a query that will give me the participants whose birthday matches today, but don't know how to get it to spread over a whole week a week in advance. (Is this making sense)?

    Here is the SQL for the query I've already created. How do I get it to look ahead instead of just at today?

    SELECT [FirstName] & " " & [Lastname] AS Name, [Address] & ", " & [Suburb] & " " & [State] & " " & [PostCode] AS Postal, Participant.DOB, Participant.SuburbID, Participant.eMailAddress, Participant.Expired, Participant.Inactive
    FROM Suburb INNER JOIN Participant ON Suburb.SuburbID = Participant.SuburbID
    WHERE (((Participant.Expired)=False) AND ((Participant.Inactive)=False) AND ((Month([DOB]))=Month(Date())) AND ((Day([DOB]))=Day(Date())));

    Any suggestions?

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    One way would be to build a date value that included the month and day from the DOB along with the current year, and compare that to the 2 desired dates (ie today and a week from today) using Between.
    Paul

  3. #3
    Join Date
    May 2006
    Posts
    25

    Building the date value?

    Okay, I get the gist of what you are saying, however, I don't know how to build the Birth Date so that it has the current year.

    So far I have: Birthday: Format([DOB],"dd/mm") am I heading in the right direction?

    For the Between statement I have: Between (DateAdd("d",7,Date())) And (DateAdd("d",14,Date())) so that it starts looking a week ahead and compiles a week's worth of dates.

    Let me know where I may be right and where I am terribly WRONG

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    extracting the day and month isn't robust enough, because what if you run the query on december 27th?

    do a search on this site for "birthdays" -- this question has been solved before
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2006
    Posts
    25
    Thanks for that! Found what I needed on this thread: http://www.dbforums.com/showthread.p...ight=birthdays

Posting Permissions

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