Parameter query as a controlsource

[Kath]

I have a parameter query with 'From' and 'To' dates that I create a report with. Is there anyway to use the From and To dates as a control source for the report ? I'd like the report page to include the dates as a header, since they will be changing. Thanks in Advance if anyone can help!!

 

[pbaldy]

I assume you're using the brackets method (I'd use a form). You can use the same thing in the report:

=[Enter from date]

 

[StuckInLiverpool]

Yes, in the header space, enter:

=[reports]![name of thereport]![what you've written in the first parameter box, eg Start Date]

Repeat for the end date, changing the last field above

Hope that helps.

 

[vbaInet]

I'd like the report page to include the dates as a header, since they will be changing.

For the From date use:

=Min([DateField])

For the To date use:

=Max([DateField])

... where DateField is the name of your Date/Time field.

 

[pbaldy]

I would not use Min and Max, as they wouldn't necessarily match up to the input parameters (in the event there was no data on a given date). It could lead to confusion over what dates the report was run for.

 

[vbaInet]

Yes, I was going to mention the caveat to that but I just assumed it was just a straightforward report bound to a parameter query. If one or none of the parameters were filled in, then Min and Max will also display nothing.

 

[pbaldy]

I made the same assumption. My point is that if you run a report for november 1 thru 30 and there's no data for the 30th, your method will display the 29th, which would be misleading.

 

[vbaInet]

I made the same assumption. My point is that if you run a report for november 1 thru 30 and there's no data for the 30th, your method will display the 29th, which would be misleading.

I see what you mean now Paul. Makes sense!

So here's another suggestion (in addition to the others):

Put these two parameters as new columns in your query and reference them in your report.

E.g.:

SELECT Field1, Field2, DateField, [COLOR=Red][Enter From] [/COLOR]As [COLOR=Blue]FromDate[/COLOR], [COLOR=Red][Enter To][/COLOR] As [COLOR=Blue]ToDate[/COLOR]
FROM TableName
WHERE DateField BETWEEN [COLOR=Red][Enter From][/COLOR] To [COLOR=Red][Enter To][/COLOR];

Then you can reference FromDate and ToDate in your report.

But like Paul, I will use a form.