How do I get the date range that must be entered for my query to .

[Guest]

My simple query has a filter that required the date range to be selected [one
day, one week, one year etc] - and I want to display the selected date range
on the report it generates. I would hate my customer to have to hand write
the date range on the report once they've printed it.

Sorry if anyone has already replied to this question - first time user and
still getting use to this forum.

 

[Nikos Yannacopoulos]

Sue,

The common approach to this is to use a control (or two, for a range)
ona form for the user to type in (or select, if you use calendar
controls or listboxes or combos) the date range. Then the query can
"read" the criteria from the form, and so can the report as long as the
form is still open. Assuming your form is called frmReportCriteria and
has two controls, ctlDateFrom and ctlDateTo, the criterion in the query
would be:

Between Forms![frmReportCriteria]![ctlDateFrom] And
Forms![frmReportCriteria]![ctlDateTo]

(watch out for wrapping in your newsreader)

In your report, you can reference the controls on the form using the
same syntax, i.e. Forms![FormName]![ControlName].
If you use textboxes, make sure they are formatted as date!

Now, going a step further, you could very easily add a button on the
same form to open the report, with the help of the button wizard, and
give a more professional look to your app.

HTH,
Nikos

 

[Steve]

To add to what Nikos said, you need a way in frmReportCriteria to hide the
form after you enter the criteria. You can do this either in the AfterUpdate
event of the second textbox or with a button. The code would be:
Me.Visible = False
frmReportCriteria must stay open as long as the report is open. Say you
first preview the report. The report must first read the criteria. If you
then print the report, the report must read the criteria again. If you close
frmReportCriteria after opening the report for preview, the criteria will
not be available when you go to print. So you close frmReportCriteria in the
Close event of the report with:
DoCmd.Close acform, "frmReportCriteria"

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Sue,

The common approach to this is to use a control (or two, for a range)
ona form for the user to type in (or select, if you use calendar
controls or listboxes or combos) the date range. Then the query can
"read" the criteria from the form, and so can the report as long as the
form is still open. Assuming your form is called frmReportCriteria and
has two controls, ctlDateFrom and ctlDateTo, the criterion in the query
would be:

Between Forms![frmReportCriteria]![ctlDateFrom] And
Forms![frmReportCriteria]![ctlDateTo]

(watch out for wrapping in your newsreader)

In your report, you can reference the controls on the form using the
same syntax, i.e. Forms![FormName]![ControlName].
If you use textboxes, make sure they are formatted as date!

Now, going a step further, you could very easily add a button on the
same form to open the report, with the help of the button wizard, and
give a more professional look to your app.

HTH,
Nikos

My simple query has a filter that required the date range to be selected [one
day, one week, one year etc] - and I want to display the selected date range
on the report it generates. I would hate my customer to have to hand write
the date range on the report once they've printed it.

Sorry if anyone has already replied to this question - first time user and
still getting use to this forum.

 

[Guest]

Hi Nikos, thanks so much for taking the time to reply. I've got four replies
and four different ways of doing this now - all of which will take me some
time to understand. That's the problem with being self-taught huh - a little
knowledge being a dangerous thing. Cheers Sue

Sue,

The common approach to this is to use a control (or two, for a range)
ona form for the user to type in (or select, if you use calendar
controls or listboxes or combos) the date range. Then the query can
"read" the criteria from the form, and so can the report as long as the
form is still open. Assuming your form is called frmReportCriteria and
has two controls, ctlDateFrom and ctlDateTo, the criterion in the query
would be:

Between Forms![frmReportCriteria]![ctlDateFrom] And
Forms![frmReportCriteria]![ctlDateTo]

(watch out for wrapping in your newsreader)

In your report, you can reference the controls on the form using the
same syntax, i.e. Forms![FormName]![ControlName].
If you use textboxes, make sure they are formatted as date!

Now, going a step further, you could very easily add a button on the
same form to open the report, with the help of the button wizard, and
give a more professional look to your app.

HTH,
Nikos

My simple query has a filter that required the date range to be selected [one
day, one week, one year etc] - and I want to display the selected date range
on the report it generates. I would hate my customer to have to hand write
the date range on the report once they've printed it.

Sorry if anyone has already replied to this question - first time user and
still getting use to this forum.

 

[Guest]

Hi Steve, thanks so much for taking the time to reply. I've got four replies
and four different ways of doing this now - all of which will take me some
time to understand. That's the problem with being self-taught huh - a little
knowledge being a dangerous thing. Cheers Sue

To add to what Nikos said, you need a way in frmReportCriteria to hide the
form after you enter the criteria. You can do this either in the AfterUpdate
event of the second textbox or with a button. The code would be:
Me.Visible = False
frmReportCriteria must stay open as long as the report is open. Say you
first preview the report. The report must first read the criteria. If you
then print the report, the report must read the criteria again. If you close
frmReportCriteria after opening the report for preview, the criteria will
not be available when you go to print. So you close frmReportCriteria in the
Close event of the report with:
DoCmd.Close acform, "frmReportCriteria"

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Sue,

The common approach to this is to use a control (or two, for a range)
ona form for the user to type in (or select, if you use calendar
controls or listboxes or combos) the date range. Then the query can
"read" the criteria from the form, and so can the report as long as the
form is still open. Assuming your form is called frmReportCriteria and
has two controls, ctlDateFrom and ctlDateTo, the criterion in the query
would be:

Between Forms![frmReportCriteria]![ctlDateFrom] And
Forms![frmReportCriteria]![ctlDateTo]

(watch out for wrapping in your newsreader)

In your report, you can reference the controls on the form using the
same syntax, i.e. Forms![FormName]![ControlName].
If you use textboxes, make sure they are formatted as date!

Now, going a step further, you could very easily add a button on the
same form to open the report, with the help of the button wizard, and
give a more professional look to your app.

HTH,
Nikos

My simple query has a filter that required the date range to be selected [one
day, one week, one year etc] - and I want to display the selected date range
on the report it generates. I would hate my customer to have to hand write
the date range on the report once they've printed it.

Sorry if anyone has already replied to this question - first time user and
still getting use to this forum.