How to show qry "criteria" on a report ?

[Mel]

- I'm running a Report on a multi-table Query...
- With the "criteria" on a date field named PaidDate set to... >#mm/dd/
year# And <#mm/dd/year#
- I want to show the date range on the report

So I create my report with a Text Box...
Normally I want a sum so I enter... =Sum[Stuff]
and it works fine...

What do I put in the Text Box to have the Report display the date
range I entered into the query criteria field?

Thanks for any help.

 

[Douglas J. Steele]

If the dates are hard-coded into the query, I don't think there's any easy
way of getting them.

If you're prompted to enter the dates, you can add the prompts as fields in
the query, and then treat them the same as any other fields in the query.

 

[Mel_3]

1 - Please remind me how to prompt for query criteria...

2 - And how to prompt for...
....Please enter starting date
....Please enter ending date

3 And then how to plug that into a query criteria field such that dats
_from_ the starting date _through_ the ending date are returned.

4 - and then how to have the starting and ending date print on the
report.

Thanks for the help.

 

[Mel_3]

OK, I remembered... to get a query to prompt you for a start and end
date put this in the Criteria field...

Between [Enter Start Date:] and [Enter End Date:]

BUT... my question now is... if you enter 1/1/2008 and 12/31/2008 in
the above...

Does it return data from 1/1/2008... or is that date excluded?
Does it return data from 12/31/2008... or is that date excluded?

Because if someone says... pick a number between 1 and 5 it could mean
pick either 2,3, or 4...
or it could mean pick either 1,2,3,4, or 5.

 

[Mel_3]

After a quick test... the answer is...
Between[Enter Start Date:] and [Enter End Date:]
will return data between... and including... the start and end date.

 

[John W. Vinson]

1 - Please remind me how to prompt for query criteria...

2 - And how to prompt for...
...Please enter starting date
...Please enter ending date

3 And then how to plug that into a query criteria field such that dats
_from_ the starting date _through_ the ending date are returned.

4 - and then how to have the starting and ending date print on the
report.

Thanks for the help.

Create a Query based on your table.

In a vacant Field cell type

Criteria: [Please enter starting date] & " through " & [Please enter ending
date]

In the Criteria line underneath the date field (I don't know the fieldname

= CDate([Please enter starting date]) AND < DateAdd("d", 1, CDate([Please enter ending date]))

More simply but less flexibly, just use

BETWEEN [Please enter starting date] AND [Please enter ending date]

This will lose data on the last day of the range if your field contains a time
portion.

 

[Mel_3]

Using the Query Parameter...

Between [Enter Start Date:] and [Enter End Date:]

How can I capture that Start Date and End Date... and have it show up
on the report?

Thanks.

 

[John W. Vinson]

Using the Query Parameter...

Between [Enter Start Date:] and [Enter End Date:]

How can I capture that Start Date and End Date... and have it show up
on the report?

Thanks.

Did you happen to try what I suggested?

In a vacant Field cell type

Criteria:[Enter Start Date:] & "through" & [Enter End Date:]

and put a textbox on the report bound to [Criteria].

to modify it to the new prompts that you posted this time.

 

[David W. Fenton]

If you're prompted to enter the dates, you can add the prompts as
fields in the query, and then treat them the same as any other
fields in the query.

If you're collecting the criteria in the OnOpen event of the report
(as is my usual practice), you can stuff them into module-level
variables in the report and then controls anywhere on the report
from there. You don't need variables if you're displaying the
criteria in the header, though -- just assign the value to a
textbox, or the Caption to a label.

 

[Mel_3]

John, I haven't had a chance to try your suggestion yet. Late here.
Will try it tomorrow morning.

Thanks for the help everyone !

 

[BruceM]

As a suggestion for future postings, investigate as much as you can before
posting. For instance, as you discovered a simple test can tell you whether
Between includes the bracketing dates. I think you will find people are
more willing to help those who do as much advance work as possible before
posting a question.