Birthdays this month

[Guest]

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.

 

[Guest]

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.

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

(no e-mails, please!)

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]

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

(no e-mails, please!)

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.

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

(no e-mails, please!)

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

 

[Guest]

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

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

(no e-mails, please!)

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.

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

(no e-mails, please!)


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]

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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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

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

(no e-mails, please!)

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.

:

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

(no e-mails, please!)


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]

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

(no e-mails, please!)

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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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

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

(no e-mails, please!)


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.

:

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

(no e-mails, please!)


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

 

[Guest]

thanks that worked!

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

(no e-mails, please!)

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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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

:

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

(no e-mails, please!)


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.

:

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

(no e-mails, please!)


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