Trying to extract month from a date field to compare in report

[gmazza via AccessMonster.com]

Hi there,
I am trying to run a report using a parameter for where the user chooses a
month from a combo box. Then on the report, I want it to compare the month to
a date field and choose only those dates with the month chosen from the
parameter form.
Basically, its to see who's birthdays are coming up. So on the parameter form
you choose November, so then I want the report to display all the kids who's
birthdays are in November.
Any help would be appreciated as I just can't seem to extract the month from
the DateOfBirth field to compare to what they chose on the parameter form.
Thanks,
G

--
Message posted via http://www.accessmonster.com

[Fred Zuckerman]

"gmazza via AccessMonster.com" <u37142@uwewrote in message
news:78784b8cc6d54@uwe...

Hi there,
I am trying to run a report using a parameter for where the user chooses a
month from a combo box. Then on the report, I want it to compare the month
to
a date field and choose only those dates with the month chosen from the
parameter form.
Basically, its to see who's birthdays are coming up. So on the parameter
form
you choose November, so then I want the report to display all the kids
who's
birthdays are in November.
Any help would be appreciated as I just can't seem to extract the month
from
the DateOfBirth field to compare to what they chose on the parameter form.
Thanks,
G

--
Message posted via http://www.accessmonster.com

Try setting the filter in the report open event.

' caution - air code:
Private Sub Report_Open(Cancel As Integer)
Me.Filter = "Month([DateOfBirth])=" & Forms!frmMain!cboMonth
Me.FilterOn = True
End Sub

This assumes that your combo box control is named "cboMonth"
And the form is named "frmMain".

Another Suggestion:
It seems that you have the user select the desired month with the combo box
and then presumably click a button to run the report. This requires that the
form be open whenever you run the report. Normally, this might be fine. But
I find it to be a pain when doing development work, testing new reports, or
executing reports from the database window. Instead, I like to solicit the
user input in the report open event (when the input is relatively simple).
This allows the report to be a truly stand alone object, not dependant on
any other forms being open.

Here's how I do it:

' caution - air code:
Private Sub Report_Open(Cancel As Integer)
Dim intMonth As Integer
intMonth = Cint(InputBox("Enter Desired Month (ie: 1-12)"))
Me.Filter = "Month([DateOfBirth])=" & intMonth
Me.FilterOn = True
End Sub

Good Luck,
Fred Zuckerman

[Bob Quintal]

"gmazza via AccessMonster.com" <u37142@uwewrote in
news:78784b8cc6d54@uwe:

Hi there,
I am trying to run a report using a parameter for where the user
chooses a month from a combo box. Then on the report, I want it to
compare the month to a date field and choose only those dates with
the month chosen from the parameter form.
Basically, its to see who's birthdays are coming up. So on the
parameter form you choose November, so then I want the report to
display all the kids who's birthdays are in November.
Any help would be appreciated as I just can't seem to extract the
month from the DateOfBirth field to compare to what they chose on
the parameter form. Thanks,
G

Month([dateOfBirth]) returns a number from 1 to 12.
In the combobox, make sure that the bound column is the numeric
value for the month. It can be hidden, so the combobox just shows
the month name.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[gmazza via AccessMonster.com]

Hi there,
Maybe I'm explaining it wrong, or its not working as it is saying it can't
find frmMain when I try and run the report, maybe cause I do make the form
invisible when I run the report but I commented that out and I am getting a
different error. Here is a little more detail:
When the user clicks the report button from the main menu, a parameter form
comes up asking to choose a month. Once they choose the month, they click a
button saying run report. The control source of my report is a query which is
where I am trying to pull the month from the DateOfBirth field to compare to
the month they entered on the parameter form.
Thanks for your help so far, I hope I'm making sense, and I hope I am
understanding what you are trying to get me to do.
G

Fred Zuckerman wrote:

>Hi there,
I am trying to run a report using a parameter for where the user chooses a

[quoted text clipped - 12 lines]

>Thanks,
G

Try setting the filter in the report open event.

' caution - air code:
Private Sub Report_Open(Cancel As Integer)
Me.Filter = "Month([DateOfBirth])=" & Forms!frmMain!cboMonth
Me.FilterOn = True
End Sub

This assumes that your combo box control is named "cboMonth"
And the form is named "frmMain".

Another Suggestion:
It seems that you have the user select the desired month with the combo box
and then presumably click a button to run the report. This requires that the
form be open whenever you run the report. Normally, this might be fine. But
I find it to be a pain when doing development work, testing new reports, or
executing reports from the database window. Instead, I like to solicit the
user input in the report open event (when the input is relatively simple).
This allows the report to be a truly stand alone object, not dependant on
any other forms being open.

Here's how I do it:

' caution - air code:
Private Sub Report_Open(Cancel As Integer)
Dim intMonth As Integer
intMonth = Cint(InputBox("Enter Desired Month (ie: 1-12)"))
Me.Filter = "Month([DateOfBirth])=" & intMonth
Me.FilterOn = True
End Sub

Good Luck,
Fred Zuckerman

--
Message posted via http://www.accessmonster.com