Re: Date range on reports

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
> 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
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>


.