Link to home
Create AccountLog in
Avatar of SteveL13
SteveL13Flag for United States of America

asked on

How query table for birthdays in a selected month

I have a form that has a field that the user can select a month name from.  (January, February, etc.)  Then I want to query  the table that has employee names and birthdays.  But the birthdays are stored as for example, 2/15/1985 or 12/30/2010.

How can I query those employees for the birthdays in the month selected?
Avatar of PatHartman
PatHartman
Flag of United States of America image

Select ....
From ...
Where Month(DOB) = Forms!yourform!cboMonth
Order By Day(DOB);
Avatar of SteveL13

ASKER

Hmmm, isn't working.

Here is my SQL:

SELECT [LAST NAME] & ", " & [FIRST NAME] AS [Player Name], [mail list].DateOfBirth INTO TEMPBirthdayList
FROM [mail list]
WHERE (((Month([DateOfBirth]))=[Forms]![frmBirthdayList]![cboMonth]))
ORDER BY [LAST NAME] & ", " & [FIRST NAME];

Open in new window


But for example, if I select "August" from the combobox, I get no records.

Does it matter that the combobox is a value list?  Using:

"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December"

--Steve
This function does exactly that:
Public Function IsBirthday( _
  ByVal datDateOfBirth As Date, _
  Optional varDate As Variant) _
  As Boolean

' Returns True if date varDate (or today) is an annual birthday.
' Validates correctly Feb. 29. as Feb. 28. for non leap years and
' excludes annual birthdays earlier than the date of birth.
'
' Gustav Brock, Cactus Data ApS.
' 2000-12-20.
' 2008-05-12. Converted to stand-alone function.

  Dim datDate     As Date
  Dim booBirthday As Boolean
  
  ' No special error handling.
  On Error Resume Next
  
  If IsDate(varDate) Then
    datDate = Fix(CDate(varDate))
  Else
    datDate = Date
  End If
  booBirthday = (DateDiff("d", datDate, DateAdd("yyyy", Abs(DateDiff("yyyy", datDateOfBirth, datDate)), datDateOfBirth)) = 0)

  IsBirthday = booBirthday

End Function

Open in new window

/gustav
But then how do I use the function in a maketable query?
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Modify the SQL:
SELECT 
    [LAST NAME] & ", " & [FIRST NAME] AS [Player Name], [mail list].DateOfBirth 
INTO 
    TEMPBirthdayList
FROM 
    [mail list]
WHERE
    Month([DateOfBirth])=[Forms]![frmBirthdayList]![cboMonth]
    AND
    IsBirthday([DateOfBirth]) = True
ORDER BY 
    [LAST NAME] & ", " & [FIRST NAME];

Open in new window

/gustav
This suggestion worked perfectly.  Thank you.
Please build query function as mentioned

Expr1: MonthName(Month([Table1]![DB]),False)

Note: Expr1 is dummy expression which will be created in query and Table1 is table name and DB is birthday date.

Then you can create form accordingly.