Avatar of SteveL13
SteveL13
Flag 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?
Microsoft Access

Avatar of undefined
Last Comment
Amit Kumar

8/22/2022 - Mon
PatHartman

Select ....
From ...
Where Month(DOB) = Forms!yourform!cboMonth
Order By Day(DOB);
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
Gustav Brock

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
SteveL13

ASKER
But then how do I use the function in a maketable query?
ASKER CERTIFIED SOLUTION
Gustav Brock

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Gustav Brock

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
SteveL13

ASKER
This suggestion worked perfectly.  Thank you.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Amit Kumar

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.