Min and Max date

[hmongie]

Hi,

Hope someone can help.

I have a simple report. The report has a bunch or fields.

I've created two unbound fields in my report header. This is where i'm having difficulties. I need the first field to list the min date and the second field to list the max date. This is to show that the records are from 01/01/07 to 01/30/07 or what ever is inputted into the search query.

EX. If I used a query to pull the report for particular dates. Such as between 01/01/07 and 01/30/07. the date is just the date that a person had purchased a bike. The field is called [buydate].

The min date should say 01/01/07 and the max date should say 01/30/07. This should change when the user decides to query for a different date range.

Any help or ideas, please let me know.

 

[statsman]

In the criteria of the date field of your query try:

Between [Start Date] And [End Date]

The query should prompt you for "Start Date" and "End Date" which you would enter. You should get results between the two dates.

 

[hmongie]

Hi Statsman,

I have the criteria setup already. It's in the report header that I need the min date and max dates to show. Any ideas?

I have something like

=min(BuyDate) <-- unbound field
and
=max(BuyDate) <-- unbound field.

But it errors.

 

[pbaldy]

A text box with a source of:

=[Enter start date]

should pull what the user entered, as long as the text is exactly the same as in the query criteria.

 

[hmongie]

Hmmm. interesting..

I have a query set up with this.
Between [Type the beginning date: EX: 01/01/04] And [Type the ending date: EX: 01/30/04]

When the users enters 01/03/07 and 01/05/07 as the dates, I would like the dates to appear in the report header.

Something like; "Records from purchase dates 01/03/07 to 01/05/07"
It shows that the report was ran for those dates.

01/03/07 is the date that was inputed into the query as the begin date.
01/05/07 is the date that was inputted into the query as the end date.

any ideas?

 

[pbaldy]

I must have been unclear.

="Records from purchase dates " & [Type the beginning date: EX: 01/01/04] & " to " & [Type the ending date: EX: 01/30/04]

 

[hmongie]

Paul, I think I'm probably not clear on the question.

I have the query already.


I have a display form. On the display form, the user clicks a button to activate a macro. The macro opens the report but the macro has a query also. The query ask the user for the dates. The user inputs in the dates. The report returns the records based on what the user had entered.

Now here where I'm stuck.

If the user inputs 01/02/07 for the begin date and 01/03/07 for the end date the records will be displayed on the report. But, I would like the dates that were inputted by the user during the query to show on the report header. This is to show that user john doe had queried from 01/02/07 to 01/03/07. These dates are not saved anywhere. They are just the dates that the user had inputted.

These dates looks at one field. It's call [BuyDate].

Hope I'm not confusing you more.

 

[pbaldy]

Not sure what you mean when you say the macro runs a query. If the query with the criteria is the source of the report, then what I gave you as the control source of a textbox will give you what you're asking for.

 

[hmongie]

In my macro, it opens report BUYERREPORT, under the "Filter Name" I have it to run my query "buydaterange".

buydaterange ask the user for the begin date and end date.

I have it this way because I have these other macros using the same report but with different query. Such as for locations, male or female and so forth, but all based on the date of purchase.

Your example looks good but it doesn't filter the report dates to the dates that is needed. It just displays the date that the user had inputted.

 

[pbaldy]

Well, this:

"It just displays the date that the user had inputted"

seems like exactly what you asked for:

"When the users enters 01/03/07 and 01/05/07 as the dates, I would like the dates to appear in the report header"

so I guess I'm completely confused as to what you want. Maybe a sample would help.

 

[statsman]

What Paul was telling you is correct.
Go to the query. Copy exactly what you entered in the square brackets in the criteria into text boxes on your report. Or better yet use control C and Control P to copy and paste it. In your case:
[Type the beginning date: EX: 01/01/04] first text box
and
[Type the ending date: EX: 01/30/04] second text box.

This should give you the start and end date for the report data.