Form with date range & macro to run report but dates don't work

[Lorina]

Hi
I have a very large report (600 pages) and I want the user to be able to
enter a date range. I set up a form, linked it to the report. I set up 2
text boxes for them to enter the from and to date ranges. Then I added a
command button (no wizard) and created a macro to open the report and then
open the form

The first time I entered a date range it was perfect! Now, no matter what
dates I put in I get the same dates as the first time! How do I make it
take the dates entered?
Also, my macro is (1) open report (2) open form. is that backwards ? do I
need anythign after that?

thanks! This has been driving me crazy

 

[Steve]

Where are you opening the form from? Change the code there to open your
report. Then open the report in design view, click on Properties and go to
the Event tab. In the Open event add this code to open your form:
DoCmd.OpenForm "NameOfYourForm",,,,acDialog

On your form you need a button with the caption "View Report". Put the
following code in the click event of the button:
Me.Visible = False

Finally, open your report in design view, Click on Properties, go to the
Event tab, and put the following code in the close event:
DoCmd.Close "NameOfYourForm"

Steve
(e-mail address removed)

 

[John Spencer]

You have to open the form BEFORE you open the report.

Normally, you would open the form, enter the criteria, and press a
button that opens the report.

The code behind the button could pass a string limiting the records in
the report or the report's source could be limited by having the query
refer to the controls on the form.

The VBA code behind the button on the form might look like the
following. Assumptions: two controls named txtStartDate and txtEndDate
and the datefield (YourDateField) is included on your report.

Dim strWhere as string
Const strDateFormat as string = "\#yyyy-mm-dd\#"

strWhere = "YourDateField Between " & _
Format(Me.txtStartDate,strDateFormat) & _
" AND " & Format(Me.txtEndDate,strDateFormat

DoCmd.OpenReport "MyReport",,,strWhere

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================