Birthdays this month

[Chuck]

Hi, i have a form (frmbirthdays) with a combo box (cboMonth) with months of
the year starting at January, February etc. I have a subform
(subfrmBirthdays) with following fileds; client name, gender, Client_DoB,
Current Age, Happy Birthday.
Current Age is calculated with formula:
=DateDiff("yyyy",[Client_DoB],Now())+Int(Format(Now(),"mmdd")<Format([Client_DoB],"mmdd"))

Client_DoB is Date Data Type. dd/mm/yyyy
I would like the subfrm filtered by the month in the combo box when selected
to reflect birthdays in November for example. I would also like Happy
Birthday to reflect Age + 1 but only if the curent date is < the DoB. Can you
help with VBA please...thanks

[Douglas J. Steele]

Try using =DateDiff("yyyy",[Client_DoB],Now()) for the Happy Birthday field.

To retrieve only those records with birthdays in whatever month has been
selected in cboMonth, add a computed field Format([Client_DoB], "mmmm") to
your query, and set Forms!frmBirthsdays!cboMonth as its criteria. In the
AfterUpdate event of the combo box, put code to requery the subform:
Me.NameOfSubformControl.Form.Requery. Make sure that you use the name of the
subform control on the form: depending on how you added subfrmBirthdays as a
subform, the name of the control might not be subfrmBirthdays.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Chuck" <Ch...@discussions.microsoft.com> wrote in message
news:D63B18B1-E4DC-46E3...@microsoft.com...

[Chuck]

Hi Doug, can you help me with the actual VBA code statements - I have added
a Filter button that should execute the vba code, ie the cboMonth will be
just used to select the the appropriate month - then you press Filter button.

[Douglas J. Steele]

Private Sub Filter_Click()

Me!NameOfSubformControl.Form.Requery

End Sub

It may well be that the subform control on the main form is also named
subfrmBirthdays, in which case the line would be

Me!subfrmBirthdays.Form.Requery

However, only you can determine that.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Chuck" <Ch...@discussions.microsoft.com> wrote in message

news:4490DEFB-B053-4EB8...@microsoft.com...

[Chuck]

Thanks Doug, i used all your suggestions except i created following code to
ues the cboMonth
Private Sub cboMonth_AfterUpdate()
Set SF = Me.[subfrmBirthdays].Form ' Reference the Subform through it's
*control*

SF.Filter = "[Month] = """ & Me.cboMonth & """"
SF.FilterOn = True

'Requery subform
Me!subfrmBirthdays.Form.Requery
End Sub

Once I've filtered the month, now i want to sort the Client_DoB field by dd
ascending so 01-Dec-1948 is first, 02-Dec-1980 is second etc. How do i do
that as part Case1 below?
Private Sub Filter_Click()
Dim FrmS As Access.Form

Set SF = Me.[subfrmBirthdays].Form ' Reference the Subform through it's
*control*

Select Case Me.[BirthdayFilterOpt].Value
Case 1 ' Sort by Day
SF.OrderBy = ??????
SF.OrderByOn = True
Case 2 ' Sort by Gender
SF.OrderBy = "[Gender] ASC"
SF.OrderByOn = True
Case 3 'Sort by Last Name
SF.OrderBy = "[Last_Name] ASC"
SF.OrderByOn = True

End Select
End Sub

[Douglas J. Steele]

Add a computed field to the underlying query Format([Client_DoB], "ddmm")
and sort on it.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Chuck" <Ch...@discussions.microsoft.com> wrote in message

news:C49D31C8-2BB8-4643...@microsoft.com...

[Douglas J. Steele]

Actually, all you need is Day([Client_DoB]) as the computed field, since
you're already limiting to a single month.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:%23qkajp2...@TK2MSFTNGP02.phx.gbl...

[Chuck]

thanks that worked!