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

"gmazza via AccessMonster.com" <u37142@uwe> wrote in message
news:78784b8cc6d54@xxxxxx
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


.

Relevant Pages

  • Re: Sub Report visibility
    ... I was using the Report Open event instead of the Detail ... Alain ... > the subfrmPropCosts and subfrmPropSales are the proper name of the reports ...
    (microsoft.public.access.reports)
  • order by
    ... Private Sub Report_Open ... I want to use report order by to sort report by using the report open event ...
    (microsoft.public.access.reports)
  • Re: Listbox Rowsource
    ... available for reports but you can't really control it. ... Pretty unusual to use a listBox in a report, but other than that you shoudl be ... than report Open event though. ... Rick Brandt, Microsoft Access MVP ...
    (microsoft.public.access.reports)
  • RE: Opening a report hangs and it does not print
    ... well my run-in with a bad print driver the printer worked fine for Word docs ... the report works then it surely is the print driver.... ... It all worked until I changed the query - but the query works fine standalone. ... I have a msgbox in the report open event. ...
    (microsoft.public.access.reports)
  • Re: Trying to extract month from a date field to compare in report
    ... 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 ... When the user clicks the report button from the main menu, a parameter form ... user input in the report open event. ...
    (comp.databases.ms-access)