Easy Date Range for Report

[Guest]

Hi,

I would like to make it very easy for users to control the date range of a
report. Is there a way I can create a simple form or text boxes in my report
for entry of the date range by the user? For example "StartDate" and
"EndDate" textboxes in my report. I want to avoid having users enter date
ranges in the actual query.

Alternate solutions also appreciated.

Thanks,
Diana

 

[Rick B]

The simplest way is to just add parameters to your query upon which the
report is based.

Instead of putting criteria like...

Between #01/01/05# and # 01/30/05#

change it to...

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


This will prompt the user for a date range.

 

[fredg]

Hi,

I would like to make it very easy for users to control the date range of a
report. Is there a way I can create a simple form or text boxes in my report
for entry of the date range by the user? For example "StartDate" and
"EndDate" textboxes in my report. I want to avoid having users enter date
ranges in the actual query.

Alternate solutions also appreciated.

Thanks,
Diana

1) If you are using a query as record source for the report, an easy
method is to have the query prompt for the date range.

On the Criteria line of the date field in the query, write:
Between [Start Date] and [End Date]

If you also wish to show the entered date range in the report, add an
unbound control to the report header.
Set it's Control Source to something like:
="For sales between " & [Start Date] & " and " [End Date]
Note... the text inside the brackets must be identical to the
bracketed text in the query.

2) A better method would be to use a form to enter the date range in.

Create an unbound form. Add 2 Text Controls.
Name one StartDate and the 2nd EndDate.

Add a command button.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

In the query that is the record source for the report, in it's Date
field's criteria line, write:
Between forms!ParamForm!StartDate AND forms!ParamForm!EndDate

Next, code the main report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the main report's Close event:
DoCmd.Close acForm, "ParamForm"

When ready to run the report, open the report. The form will display
and wait for the entry of the dates. Click the command button and the
report will run without need for any further parameter entries. When
the report closes, it will close the form.

If you wish to display the parameters in the report, set the control
source of the unbound control to:
= For sales between " & forms!ParamForm!StartDate & " and " &
forms!ParamForm!EndDate

 

[Albert D.Kallal]

The normal approach is to build a un-bound form (a un-bound form is a form
that is NOT attached to a table - these forms are typicaly desiged for user
interface face stuff like promtps, print buttions etc).

The following screen shots are all un-bound forms, and they simply prompt
the user for informaton.


The above shold give you some ideas

So, the solution use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the reports
(or forms), and attached them to the query. And, NO FORMS conditions are
placed in the query.

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount.

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.

For a date range, we could put two calendar contorls on the screen. The code
could be:


dim strWhere as string
dim strStartDate as string
dim strEndDate as string


strStartDtae = "#" & format(me.StartDateContorl,"mm/dd/yyyy") & "#"
strEndDate = "#" & format(me.EndDateContorl,"mm/dd/yyyy") & "#"

strWhere = "InvoiceDate is between " & strStartDate & " and " & strEndDate

docmd.openReport "InvoiceReport",acViewPreview,,strWhere

 

[Guest]

Yes. Have StartDate & EndDate text boxes, formatted as perhaps Short Date, on
your form. In your query, have something like this in the criteria of your
date field:

=[Forms]![yourForm]![StartDate] and <=[Forms]![yourForm]![EndDate]

You may need to ensure that both are populated (i.e. not null) before
running the report. The easiest way to ensure that there is a start & end
date is have both default to Date() and in their BeforeUpdate events, Cancel
if null.

 

[Albert D.Kallal]

opps...here is that link of some screen shots:

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

 

[Guest]

Works great. Thanks!

The simplest way is to just add parameters to your query upon which the
report is based.

Instead of putting criteria like...

Between #01/01/05# and # 01/30/05#

change it to...

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


This will prompt the user for a date range.

--
Rick B

Hi,

I would like to make it very easy for users to control the date range of a
report. Is there a way I can create a simple form or text boxes in my report
for entry of the date range by the user? For example "StartDate" and
"EndDate" textboxes in my report. I want to avoid having users enter date
ranges in the actual query.

Alternate solutions also appreciated.

Thanks,
Diana

 

[Guest]

Thnak you Fred, your posted option 2) has helped me, too. I love it - Ones
again, THANK YOU VERY MUCHHHH!!
--
Please post all your inquiries on this community so we can all benefit -
Thank you!

Hi,

I would like to make it very easy for users to control the date range of a
report. Is there a way I can create a simple form or text boxes in my report
for entry of the date range by the user? For example "StartDate" and
"EndDate" textboxes in my report. I want to avoid having users enter date
ranges in the actual query.

Alternate solutions also appreciated.

Thanks,
Diana

1) If you are using a query as record source for the report, an easy
method is to have the query prompt for the date range.

On the Criteria line of the date field in the query, write:
Between [Start Date] and [End Date]

If you also wish to show the entered date range in the report, add an
unbound control to the report header.
Set it's Control Source to something like:
="For sales between " & [Start Date] & " and " [End Date]
Note... the text inside the brackets must be identical to the
bracketed text in the query.

2) A better method would be to use a form to enter the date range in.

Create an unbound form. Add 2 Text Controls.
Name one StartDate and the 2nd EndDate.

Add a command button.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

In the query that is the record source for the report, in it's Date
field's criteria line, write:
Between forms!ParamForm!StartDate AND forms!ParamForm!EndDate

Next, code the main report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the main report's Close event:
DoCmd.Close acForm, "ParamForm"

When ready to run the report, open the report. The form will display
and wait for the entry of the dates. Click the command button and the
report will run without need for any further parameter entries. When
the report closes, it will close the form.

If you wish to display the parameters in the report, set the control
source of the unbound control to:
= For sales between " & forms!ParamForm!StartDate & " and " &
forms!ParamForm!EndDate