Printing Date Range on reports

[Jkittle]

I did a search and did not find a solution.

I have a report that is based on a query and you have to enter Between dates. How do I get the date rage to print on the report?

Thanks for your help,
Jerry

 

[rainman89]

do you use a form to enter the dates that you want printed?

if thats the case make an unbound text box on on the form and set its value to
forms!yourformname.yourstartdatefield and same for the end date

 

[Jkittle]

Yes, I have a selection form that pops up with a calendar option to select the dates to and from.

So by the file name do mean the names of the form?

 

[Kinger43]

If you have the start date and end date boxes on your form then just add two fields to your query, one with Forms!formname.formstartdate and one with Forms!formname.formenddate in the control source field. Then in your report you can pull in these fields.

You may be able to pull them directly from the form if you don't want to add them to the query, but don't hold me to that.

 

[Jkittle]

Kinger43 & rainman89,

Thanks to both of you. Sorry I'm a little slow but I got it now.

 

[rainman89]

If you have the start date and end date boxes on your form then just add two fields to your query, one with Forms!formname.formstartdate and one with Forms!formname.formenddate in the control source field. Then in your report you can pull in these fields.

You may be able to pull them directly from the form if you don't want to add them to the query, but don't hold me to that.

hold him too it. you can pull them directly from the form. by setting the txtboxs control source = to what i said before

 

[Jkittle]

That’s exactly what I did and it worked great, again thanks!!! This site is a life saver for someone who is just learning to use the code and not just the wizards.

 

[coyote]

Date Range

Hi my friend JKittle

Are you still getting a problem with the report. On the database I posted check the report and see how you print the date selected on the Test Data Range Form.

 

[rainman89]

That’s exactly what I did and it worked great, again thanks!!! This site is a life saver for someone who is just learning to use the code and not just the wizards.

Glad to hear you got it working.

and yea. its a life saver!

 

[Jkittle]

I hate to be a pain but I do have one more report that’s a chart I can't get the date range to work. The chart is based on a query and I use the Between [Enter Beginning Date] And [Enter Ending Date] in the query. Sense the date is not a stored value how do I get the date range selected on the chart/report?

 

[Kinger43]

Without having the start and end dates as actual fields in your query, you can't. What you can do is make two new fields, Expr1:[Enter Beginning Date] and Expr2:[Enter Ending Date] in your query. Then in your report make Expr1/Expr2 the control source. When you make the report it will run the query which will ask you for the start and end dates and put these entered values in the report.

 

[Kinger43]

Hope this helps

Hope this helps

 

[boblarson]

Without having the start and end dates as actual fields in your query, you can't. What you can do is make two new fields, Expr1:[Enter Beginning Date] and Expr2:[Enter Ending Date] in your query. Then in your report make Expr1/Expr2 the control source. When you make the report it will run the query which will ask you for the start and end dates and put these entered values in the report.

To simplify everything, I would (and do) use a form as input for date selections. Not only do you get the opportunity then to include a popup date calendar (there's a good one in the samples area which doesn't require ActiveX controls) but also, if the form is left open (which should occur) you can then get the date range at the time you run the report and, if for some reason your user closes the report and wants it back again, you don't make them suffer by having to type the dates in again.

As a general guideline I would:

1. Never let your users work in tables or queries directly. Always use a form. With a form you have control over what happens.

2. Use forms for inputs instead of [Enter Such and Such] in queries. It is much cleaner, more professional, and does not irritate your users who may have to type things several times to get what they want. (for example, if you use the parameter prompt in a query, if a user enters a typo, then they have to go do it all over again and that can be frustrating.


So, it's your choice, but I would highly suggest the use of input forms. There are samples in the samples category on the forum you can use to learn how to do that.

 

[Kinger43]

boblarson is correct. I typically use a form for things like that unless I just need a quick and dirty solution.

 

[Jkittle]

I agree but I ran into a problem. I wanted to use my pop up calendar and selection form, but the field "DATE Code" that was set up before me in the table is input directly from a machine as "200710" for the date October, 2007. Even when I tried to change the format in the form it didn't like it.

 

[boblarson]

I agree but I ran into a problem. I wanted to use my pop up calendar and selection form, but the field "DATE Code" that was set up before me in the table is input directly from a machine as "200710" for the date October, 2007. Even when I tried to change the format in the form it didn't like it.

Well, if you can't use a popup calendar, it isn't the end of using forms. Your user will still need to type it in then and a form can validate the input BEFORE passing it to the query but a direct entry query parameter cannot. And, as mentioned, the form is persistent (while it is open) so they don't need to type it over and over again.

 

[Jkittle]

Thanks Bob. I'm already trying to build the form to handle the data input.

 

[coyote]

I agree but I ran into a problem. I wanted to use my pop up calendar and selection form, but the field "DATE Code" that was set up before me in the table is input directly from a machine as "200710" for the date October, 2007. Even when I tried to change the format in the form it didn't like it.

Open the table with the date field in design view. In the General area use the InputMask wizard to change the date format.

 

[kate10123]

Hi guys,

I know this an old thread but it is exactly the type of question I have been meaning to ask.

I have two unbound textboxes on a form (frm_reports) called txtFrom and txtTo

I have managed to get these to output on all the reports but how can I actually make them usable in the query that generates the report?

so that the report output is actually based on this date range.

I have tried something like this:

SELECT Count(tbl_Tutorial.StudentID) AS CountOfStudentID, tbl_Student.Nationality, tbl_Tutorial.Date
FROM tbl_Student INNER JOIN tbl_Tutorial ON tbl_Student.StudentID = tbl_Tutorial.StudentID
WHERE (((tbl_Tutorial.outcome)="Attended") AND ((tbl_Tutorial.Date) Between [forms]![frm_reports]![txtFrom] And [forms]![frm_reports]![txtTo]))
GROUP BY tbl_Student.Nationality;

but the error message that appears is as follows:

'Date is not part of an aggregate function'

I would be really grateful for any advice on this.

Thanks