Date field on Report

[Stacey]

Hello all.

This probably cannot be done, but I'm going to ask anyway.

Here is the scenerio:
I have two make table queries that both have a date prompt, >=[Enter Start Date] And <DateAdd("d",1,[Enter End Date]). These queries bring back alot of data. I have a crosstab query built against each of the tables created from those queries. Then I have a Select query to merge the two crosstabs and a third select query. The report is built on the query that merges them. I need the dates entered in the prompt to show up on the report heading. Actually, what I have done is inserted another field in the make table queries that reformats the date field to mm/yyyy. That is what I want to show up. However, because the third select query that the merge is done with has entries with no corresponding data in the other two Select queries, when I put the date field on the report, it is coming back blank. (The first record has a blank in the date field, I am guessing that's why it's coming back blank).

If I haven't totally confused you, I would appreciate any suggestions on how to get the end result I am looking for.

Thanks!

 

[Pat Hartman]

Rather than prompting for the dates you can use a form and enter the dates there. Then you can reference the form fields in the queries and in the report. Note that the form with the paramters needs to stay open until the report is printed or closed.

Select ...., Forms!YourForm!FromDate as FromDate, Forms!YourForm!ThruDate as ThruDate
From ..
Where SomeDate between Forms!YourForm!FromDate and Forms!YourForm!ThruDate;

 

[Stacey]

Thank you! That will work great, then I won't have to have 2 separate sets of date prompts either (one for each make table query). The user can type the dates in once, and each query can reference the form field.

Thanks again for the idea!
Stacey