Report Dates (start end dates)

[RustyRick]

I've got to build a daily report which will put in the header the start date and end date of the data summarized.

I have the query boxes asking me to input the dates Start and End. And that will change every day. And the report tells me how many days but not the "Dates". Start and End.

Thanks in advance.

 

[boblarson]

From your description, you are using a parameter prompt in your query to get the dates. Personally, I would use a form so the user doesn't have to re-enter it every time if they get one of them wrong. And, they can run the report multiple times if necessary without constantly having to type out the date.

But, you can use the parameter prompts if you want. Just put a text box on the report and set the control source to

="Between " & [Enter Start Date] & " And " & [Enter End Date]

Change the parts in red to match EXACTLY the parameter prompts you are using in your query.

 

[RustyRick]

Thanks for your suggestion. However. My report is querying the last days records. So the parameters move ahead every day by one day. So I don't want to enter the dates 2 times. But would like the report to automatically report the date parameters in the header.

So your suggestion make me input the dates 2 times.

 

[boblarson]

Thanks for your suggestion. However. My report is querying the last days records. So the parameters move ahead every day by one day. So I don't want to enter the dates 2 times. But would like the report to automatically report the date parameters in the header.

So your suggestion make me input the dates 2 times.

Post the SQL of your query.

 

[RustyRick]

I don't know how to do that

 

[RustyRick]

SELECT tblLogBook.Log_Book_Nbr, tblLogBook.Date, tblLogBook.Driver_ID, tblLogBook.OffDuty, tblLogBook.SleeperBirth, tblLogBook.Driving, tblLogBook.[OnDutyNot Driving], tblLogBook.Unit_No, tblLogBook.Miles
FROM tblLogBook
WHERE (((tblLogBook.Date) Between [Enter Start Date "MM/DD/YY"] And [End Date MM/DD/YY]) AND ((Date())>7));

 

[boblarson]

I don't know how to do that

1. What is in the Report's Record Source property? Open the form in design view and look on the property sheet under the DATA tab.

2. It should be the name of a query or it could be a Select statement. If it starts with the word SELECT and then a space, it would be a select statement. If that is it then all I need is for you to copy that entire line in the record source property.

3. If it is a named query, open the query in design view and then under the VIEW tab, select SQL VIEW, which should then show a bunch of words starting with the word SELECT and ending with a semi colon ";" and then copy and paste that entire bunch of words here.

 

[RustyRick]

SELECT tblLogBook.Log_Book_Nbr, tblLogBook.Date, tblLogBook.Driver_ID, tblLogBook.OffDuty, tblLogBook.SleeperBirth, tblLogBook.Driving, tblLogBook.[OnDutyNot Driving], tblLogBook.Unit_No, tblLogBook.Miles
FROM tblLogBook
WHERE (((tblLogBook.Date) Between [Enter Start Date "MM/DD/YY"] And [End Date MM/DD/YY]) AND ((Date())>7));

 

[boblarson]

SELECT tblLogBook.Log_Book_Nbr, tblLogBook.Date, tblLogBook.Driver_ID, tblLogBook.OffDuty, tblLogBook.SleeperBirth, tblLogBook.Driving, tblLogBook.[OnDutyNot Driving], tblLogBook.Unit_No, tblLogBook.Miles
FROM tblLogBook
WHERE (((tblLogBook.Date) Between [Enter Start Date "MM/DD/YY"] And [End Date MM/DD/YY]) AND ((Date())>7));

So, given what you posted, what I posted originally:
="Between " & [Enter Start Date] & " And " & [Enter End Date]

Should be modified to this:

="Between " & [Enter Start Date "MM/DD/YY"] & " And " & [End Date MM/DD/YY]

The expression has to match your parameter prompts EXACTLY.

EDIT: Actually that will give you an error due to the double quotes. You need to change your query to use SINGLE QUOTES in the parameter prompt: 'MM/DD/YY'

and then change the expresson to that too.

 

[RustyRick]

I must be missing something. Too old to do this stuff maybe.

It fills the text box nicely with the date. But I have to enter "Start Date" and "End Date" 2 times. ???

 

[RustyRick]

I would think that if the query selects the dates for the report then those dates must be available somehow to populate a text box, or at least only have to enter the dates once?

 

[boblarson]

I must be missing something. Too old to do this stuff maybe.

It fills the text box nicely with the date. But I have to enter "Start Date" and "End Date" 2 times. ???

And

I would think that if the query selects the dates for the report then those dates must be available somehow to populate a text box, or at least only have to enter the dates once?

As I have previously stated, you should be using a FORM for input from the user as far as dates go and then you can, as long as that form remains open (it can be hidden but must remain open while the report is open), you can refer to it in the query's criteria INSTEAD of parameter prompts and you can refer to it in the control source of a control on the report.

See here for an example of a form (unbound) to select dates:
http://downloads.btabdevelopment.com/Samples/reports/Sample-GenericReport.zip

In fact, if you remove the criteria from the query completely you can just pass it in the code to open the report. I believe my example also shows that.

 

[RustyRick]

ah ha lights came on, thanks, I thought I needed to use the same form as I did to initially populate the table. But using a separate form for setting the query parameters looks cool. Thanks