Re: Date range on reports

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
.