Re: Date range on reports

this is step by step what i did to be able to request date range criteria for
the report. Sorry if its a bit long. Every in the below was done to the
letter.

Hope this is what you meant...................... Cheers Mark

Create an unbound form (unbound form or report: A form or report that isn't
connected to a record source such as a table, query, or SQL statement. (The
form's or report's RecordSource property is blank.)) that prompts for report
criteria (criteria: Conditions you specify to limit which records are
included in the result set of a query or filter.).
How?

In the Database window (Database window: The window that appears when you
open an Access database or an Access project. It displays shortcuts for
creating new database objects and opening existing objects.), click Forms
under Objects.
Click the New button on the Database window toolbar.
In the New Form dialog box, click Design View, and click OK.
In Design view (Design view: A window that shows the design of these
database objects: tables, queries, forms, reports, macros, and data access
pages. In Design view, you can create new database objects and modify the
design of existing ones.), set the following form properties. Property
Setting
Caption Name you want to appear in the title bar of the form
DefaultView Single Form
AllowFormView Yes
AllowDatasheetView No
AllowPivotTableView No
AllowPivotChartView No
ScrollBars Neither
RecordSelectors No
NavigationButtons No
BorderStyle Dialog

Click the Text Box tool on the toolbox (toolbox: A set of tools that is
available in Design view to add controls to a form, report, or data access
page. The toolset available in page Design view is different from the toolset
available in form and report Design view.) to add a text box to the form for
each criteria you want to enter.
Set the properties for the text boxes as follows. Property Setting
Name Name that describes the type of criteria; for example, BeginningDate.
Format Format that reflects the data type (data type: The characteristic of
a field that determines what type of data it can hold. Data types include
Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant
(default).) of the criteria. For example, for a date criteria, select a
format such as Medium Date.

Save the form and give it a name, such as Sales Dialog.
You'll add OK and Cancel command buttons to the form after you create macros
for them.

In the Database window (Database window: The window that appears when you
open an Access database or an Access project. It displays shortcuts for
creating new database objects and opening existing objects.), click Macros ,
and then click the New button on the Database window toolbar.
Create a macro that opens the Sales Dialog form.
How?

Begin by clicking Macro Names to display the Macro Name column. Type a
macro name, such as Open Dialog, in the Macro Name column, and then click the
OpenForm action (action: The basic building block of a macro; a
self-contained instruction that can be combined with other actions to
automate tasks. This is sometimes called a command in other macro
languages.). Then set the action arguments (action argument: Additional
information required by some macro actions. For example, the object affected
by the action or special conditions under which the action is carried out.)
as follows. Argument Setting
Form Name Sales Dialog
View Form
Data Mode Edit
Window Mode Dialog

Add a second action, CancelEvent, that cancels previewing or printing the
report when the Cancel button on the form is clicked.

If the Condition column is not displayed, click Conditions .

Type the following expression (expression: Any combination of mathematical
or logical operators, constants, functions, and names of fields, controls,
and properties that evaluates to a single value. Expressions can perform
calculations, manipulate characters, or test data.) in the Condition column:

Not IsLoaded("Sales Dialog")

Note IsLoaded is a function defined in the Utility Functions module in the
Northwind sample database. It's used to check whether a form is open in Form
view (Form view: A window that displays a form to show or accept data. Form
view is the primary means of adding and modifying data in tables. You can
also change the design of a form in this view.) or Data*** view (Data***
view: A window that displays data from a table, form, query, view, or stored
procedure in a row-and-column format. In Data*** view, you can edit fields,
add and delete data, and search for data.). You must define the IsLoaded
function in your database before you can use it. (You can copy and paste this
function from Northwind into a utility module in your database.)

Create a macro that closes the form.

How?

Give the macro a name, such as Close Dialog. Click the Close action (action:
The basic building block of a macro; a self-contained instruction that can be
combined with other actions to automate tasks. This is sometimes called a
command in other macro languages.). Then set its action arguments (action
argument: Additional information required by some macro actions. For example,
the object affected by the action or special conditions under which the
action is carried out.) as follows:

Argument Setting
Object Type Form
Object Name Sales Dialog
Save No

Create a macro for the OK button.

How?

This macro hides the form. Give the macro a name, such as OK, and click the
SetValue action (action: The basic building block of a macro; a
self-contained instruction that can be combined with other actions to
automate tasks. This is sometimes called a command in other macro
languages.).Then set its action arguments (action argument: Additional
information required by some macro actions. For example, the object affected
by the action or special conditions under which the action is carried out.)
as follows:

Argument Setting
Item [Visible]
Expression No

Create a macro for the Cancel button.

How?

This macro closes the form. Give the macro a name, such as Cancel, and click
the Close action (action: The basic building block of a macro; a
self-contained instruction that can be combined with other actions to
automate tasks. This is sometimes called a command in other macro
languages.). Then set its action arguments (action argument: Additional
information required by some macro actions. For example, the object affected
by the action or special conditions under which the action is carried out.)
as follows:

Argument Setting
Object Type Form
Object Name Sales Dialog
Save No

Save and close the macro group. Give the macro group a name— for example,
the same name that you gave the unbound form.
Add OK and Cancel command buttons to the form.
How?

Reopen the Sales Dialog form in Design view (Design view: A window that
shows the design of these database objects: tables, queries, forms, reports,
macros, and data access pages. In Design view, you can create new database
objects and modify the design of existing ones.).
Make sure Control Wizards in the toolbox (toolbox: A set of tools that is
available in Design view to add controls to a form, report, or data access
page. The toolset available in page Design view is different from the toolset
available in form and report Design view.) isn't selected and create an OK
command button.
Set its properties as follows. Property Setting
Name OK
Caption OK
Default Yes
OnClick Name of the macro; for example, Sales Dialog.OK

Create a Cancel command button, and set its properties as follows. Property
Setting
Name Cancel
Caption Cancel
OnClick Name of the macro; for example, Sales Dialog.Cancel

Save and close the form.
Enter the criteria in the underlying query or stored procedure for the
report.
How?

Open the underlying query or stored procedure for the report in Design view
(Design view: A window that shows the design of these database objects:
tables, queries, forms, reports, macros, and data access pages. In Design
view, you can create new database objects and modify the design of existing
ones.).
Enter the criteria for the data. In the expression (expression: Any
combination of mathematical or logical operators, constants, functions, and
names of fields, controls, and properties that evaluates to a single value.
Expressions can perform calculations, manipulate characters, or test data.),
use the Forms object, the name of the form, and the names of the controls
(control: A graphical user interface object, such as a text box, check box,
scroll bar, or command button, that lets users control the program. You use
controls to display data or choices, perform an action, or make the user
interface easier to read.) in the criteria.
For example, in a Microsoft Access database (Microsoft Access database: A
collection of data and objects (such as tables, queries, or forms) that is
related to a particular topic or purpose. The Microsoft Jet database engine
manages the data.) (.mdb), for a form called Sales Dialog, you would use the
following expression to refer to controls named Beginning Date and Ending
Date in the query:
Between [Forms]![Sales Dialog]![Beginning Date] And [Forms]![Sales
Dialog]![Ending Date]

In a Microsoft Access project (Microsoft Access project: An Access file that
connects to a Microsoft SQL Server database and is used to create
client/server applications. A project file doesn't contain any data or
data-definition-based objects such as tables and views.) (.adp), you must
first explicitly name the parameters in the stored procedure; for example:
@Beginning_Date datetime, @Ending_Date datetime

and then use those parameters in the WHERE clause; for example:

WHERE Sales.ShippedDate Between @Beginning_Date And @Ending_Date

In an Access project, you set the reference to the controls on the form in
the InputParameters property in the report, as shown in the next procedure.

In a Microsoft Access project (Microsoft Access project: An Access file that
connects to a Microsoft SQL Server database and is used to create
client/server applications. A project file doesn't contain any data or
data-definition-based objects such as tables and views.), set the
InputParameters property in the main report.

How?

Open the report in Design view (Design view: A window that shows the design
of these database objects: tables, queries, forms, reports, macros, and data
access pages. In Design view, you can create new database objects and modify
the design of existing ones.).
Set the report's InputParameters property to a string that specifies the
parameters that are passed to the stored procedure that the report is bound
to. As in the following example, the string must be an expression
(expression: Any combination of mathematical or logical operators, constants,
functions, and names of fields, controls, and properties that evaluates to a
single value. Expressions can perform calculations, manipulate characters, or
test data.) that includes the parameters you specified in the stored
procedure and the reference to the controls on the dialog box:
@Beginning_date datetime = [Forms]![Sales Dialog]![Beginning Date],
@Ending_date datetime = [Forms]![Sales Dialog]![Ending Date]

Attach the macros to the main report.
How?

Open the report in Design view (Design view: A window that shows the design
of these database objects: tables, queries, forms, reports, macros, and data
access pages. In Design view, you can create new database objects and modify
the design of existing ones.).
Set the following report properties. Property Setting
OnOpen Name of the macro that opens the Sales Dialog form; for example,
Sales Dialog.Open Dialog
OnClose Name of the macro that closes the form; for example, Sales
Dialog.Close Dialog


"Duane Hookom" wrote:

> I don't see anything in your macro that asks for a date range. If you don't
> tell us how the dates are being requested, we can't tell you how to put the
> values in your report.
>
> --
> Duane Hookom
> MS Access MVP
> --
>
> "Mark_Milly" <MarkMilly@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:0FDA4871-7306-4619-9431-AFC5F655763C@xxxxxxxxxxxxxxxx
> > the macro is as below;
> >
> > Macro Name Condition
> > Action
> > Open Dialog
> > Openform
> > OK
> > SetValue
> > Not IsLoaded("Salaes Manger Monthly Report")
> > CANCEL
> > Close
> > Close Dialog
> > Close
> >
> > CamcelEvent
> >
> > any help would be great.
> >
> > Cheers Mark
> >
> > "Duane Hookom" wrote:
> >
> >> Would you mind sharing some information about your macro? If you want
> >> assistance, you need to provide enough information to allow someone to
> >> provide help.
> >>
> >> --
> >> Duane Hookom
> >> MS Access MVP
> >> --
> >>
> >> "Mark_Milly" <MarkMilly@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> news:01AC05F6-8EF0-428D-A3B9-6F3D1134CEC1@xxxxxxxxxxxxxxxx
> >> > Sorry Al,
> >> >
> >> > I meant "macros", i have created a macro to ask for a date range and
> >> > need
> >> > this date range to show on my report.
> >> >
> >> > Sorry for any confusion
> >> >
> >> > Cheers Mark
> >> >
> >> > "Al Camp" wrote:
> >> >
> >> >> Mark,
> >> >> Not sure what you mean by "popups."
> >> >> Do you mean you created a parameter query, and two "Input Boxes"
> >> >> come
> >> >> up
> >> >> to prompt the user for a StartDate and EndDate?
> >> >>
> >> >> If that's so, then you probably have two parameters in the query
> >> >> behind
> >> >> your report.
> >> >> Something like...
> >> >> Between [Enter Starting Date] and [Enter Ending Date]
> >> >> Given that example, an unbound text control on the report with a
> >> >> Control
> >> >> Source of...
> >> >> = "From " & [Enter Starting Date] & " to " & [Enter Ending
> >> >> Date]
> >> >> would yield the "date subtitle" your looking for in your header.
> >> >> --
> >> >> hth
> >> >> Al Camp
> >> >> Candia Computer Consulting - Candia NH
> >> >> http://home.comcast.net/~cccsolutions
> >> >>
> >> >>
> >> >> "Mark_Milly" <MarkMilly@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> >> news:90655C4C-F7C3-435D-B3C8-50490A0C389F@xxxxxxxxxxxxxxxx
> >> >> > Hi Guys,
> >> >> >
> >> >> > I have created a report for monthly sales with popups to enter the
> >> >> > date
> >> >> > range. How can I show the date range on the header of the report?
> >> >> >
> >> >> > If anyone can help it would be greatly appreiciated.
> >> >> >
> >> >> > Cheers Mark
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
.