Re: Date range on reports

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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 Datasheet view
> (Datasheet
> view: A window that displays data from a table, form, query, view, or
> stored
> procedure in a row-and-column format. In Datasheet 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
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>


.

Relevant Pages

  • Re: Date range on reports
    ... Create an unbound form (unbound form or report: A form or report that isn't ... In the Database window (Database window: The window that appears when you ... In the New Form dialog box, click Design View, and click OK. ... Begin by clicking Macro Names to display the Macro Name column. ...
    (microsoft.public.access.reports)
  • Re: Date range on reports
    ... > box to your report with a control source like: ... >> In the Database window (Database window: The window that appears when you ... >> In the New Form dialog box, click Design View, and click OK. ... >> Begin by clicking Macro Names to display the Macro Name column. ...
    (microsoft.public.access.reports)
  • Re: Date range on reports
    ... Without knowing where your report parameters were coming from, ... >>> open an Access database or an Access project. ... >>> In the New Form dialog box, click Design View, and click OK. ... >>> Begin by clicking Macro Names to display the Macro Name column. ...
    (microsoft.public.access.reports)
  • Re: Access 2003 Forms
    ... I added the 'close form' macro to the 'OnOpen' ... property of each report and added the 'open form' macro to the 'OnClose' ... I have a feeling that this problem was specific to one database only. ... > Uncheck any references you do not need. ...
    (microsoft.public.access.forms)
  • Re: Problem retrieving printer info?
    ... If it did not import again, there may be a corruption in the database. ... Tools | Database Utilities | Compact. ... You should be able to open the report in design view without having ...
    (microsoft.public.access.reports)