Date Range on Report

[Owl Lover]

Hi everyone,

I have a report in Access 2000-2003 that I need to run based on a certain
date range. I have created a Parameter query, which works, so I added two
text boxes with a Start Date and End Date, and put the query as the
RecordSource of the report. The problem is when I run the report and the
boxes pop up asking for the start date and end date, it brings up all the
records, not just the ones within that date range. I used the "Limiting a
Report to a Date Range" that Allen Browne directed me to, so what did I do
wrong?

Thanks for the help!

Owl Lover

 

[Duane Hookom]

IMHO, you should be using controls on forms rather than parameter prompts.
Allen describes a couple methods at http://www.allenbrowne.com/casu-08.html.
Which one did you use? If you used a parameter prompt, what is the SQL view
of your report's record source?

 

[Owl Lover]

Thank you for your response. I used the first example and created a
parameter query, putting the following in the Criteria row under the date
field of my report:

Between [StartDate] And [EndDate]

Then as #3 of that says, I went to the Query menu and declared two
parameters of type Date/Time; StartDate and EndDate. The query works just
fine when I run it from the design window. #4 of that article said to add
two text boxes to the Report Header section, which I did, and set their
ControlSource property to =StartDate and =End Date respectively. I also did
that, but the report returns all the records instead of only the ones within
the desired date range. I'm at work right now and my database is on my home
computer, so I can't look to see what the SQL view is. But I didn't write
any code for this if that's what you are asking. If it isn't, then I'm not
sure what you want to know. I'm sorry if I sound stupid, but I'm really not
very knowledgeable when it comes to reports and retrieving data from them.
I'm still in the beginning stages of learning. If you need more information
I will have to provide it after I get home this evening. Just let me know.

Thank you again for your help.

Owl Lover

 

[Duane Hookom]

We would need to see the record source SQL view to confirm the filtering.

I still think you should be using controls on forms so your criteria in your
query would be something like:

WHERE DateField Between Forms!frmDates!txtStart and Forms!frmDates!txtEnd

This would not require any "code" but would create a much better user
interface.

--
Duane Hookom
Microsoft Access MVP

Thank you for your response. I used the first example and created a
parameter query, putting the following in the Criteria row under the date
field of my report:

Between [StartDate] And [EndDate]

Then as #3 of that says, I went to the Query menu and declared two
parameters of type Date/Time; StartDate and EndDate. The query works just
fine when I run it from the design window. #4 of that article said to add
two text boxes to the Report Header section, which I did, and set their
ControlSource property to =StartDate and =End Date respectively. I also did
that, but the report returns all the records instead of only the ones within
the desired date range. I'm at work right now and my database is on my home
computer, so I can't look to see what the SQL view is. But I didn't write
any code for this if that's what you are asking. If it isn't, then I'm not
sure what you want to know. I'm sorry if I sound stupid, but I'm really not
very knowledgeable when it comes to reports and retrieving data from them.
I'm still in the beginning stages of learning. If you need more information
I will have to provide it after I get home this evening. Just let me know.

Thank you again for your help.

Owl Lover

IMHO, you should be using controls on forms rather than parameter prompts.
Allen describes a couple methods at http://www.allenbrowne.com/casu-08.html.
Which one did you use? If you used a parameter prompt, what is the SQL view
of your report's record source?