Re: Date range on reports

Looks as though Duane's got you covered.
Without knowing where your report parameters (dates) were coming from, we
couldn't determine how to refer to them in the report header.
Good deal...
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

"Mark_Milly" <MarkMilly@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6A8946B1-6EE8-43A3-A865-1DC313940920@xxxxxxxxxxxxxxxx
> Thanks Duane and Al, you have been more than helpful
>
> Cheers Mark
>
> "Duane Hookom" wrote:
>
>> Your dates apparently come from text boxes on your form. You can add a
>> text
>> box to your report with a control source like:
>> ="Dates Between " & [Forms]![Sales Dialog]![Beginning Date] & " And " &
>> [Forms]![Sales Dialog]![Ending Date]
>>
>>
>> --
>> Duane Hookom
>> MS Access MVP
>> --
>>
>> "Mark_Milly" <MarkMilly@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:5D31B1C8-0EA6-4ECE-A592-82C465B793CF@xxxxxxxxxxxxxxxx
>> > 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


.

Loading