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

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

.

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)
  • 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
    ... Then on the report, I want it to compare the month ... Private Sub Report_Open ... user input in the report open event. ...
    (comp.databases.ms-access)
  • Re: At least they cant blame the grey squirrels
    ... Are you saying there's no accurate counts? ... But nowhere in the report does it say that this is ... an "accurate" count as you keep insisting it is. ... monitoring amounts to and how it causes stress. ...
    (uk.environment.conservation)