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

  • Thread starter Thread starter Lorina
  • Start date Start date
L

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 :-)
 
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
'====================================================
 
Back
Top