If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date range on reports
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 |
#2
|
|||
|
|||
Date range on reports
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" wrote in message ... 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 |
#3
|
|||
|
|||
Date range on reports
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" wrote in message ... 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 |
#4
|
|||
|
|||
Date range on reports
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" wrote in message ... 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" wrote in message ... 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 |
#5
|
|||
|
|||
Date range on reports
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" wrote in message ... 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" wrote in message ... 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 |
#6
|
|||
|
|||
Date range on reports
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
#7
|
|||
|
|||
Date range on reports
Mark,
I can't see where you're asking for any date input. Are you opening a form that allows the user to enter two dates? If so, that form must stay open while the report is run. You need to provide details as to how you're trying to provide the report with the date range. If using a "dialog form", describe that form, the fields on that form, and how you run the report from that form. -- hth Al Camp Candia Computer Consulting - Candia NH http://home.comcast.net/~cccsolutions "Mark_Milly" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
#8
|
|||
|
|||
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 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
#9
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
#10
|
|||
|
|||
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" wrote in message ... 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 |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Reports with Date Range Errors -- Repost | jwr | Setting Up & Running Reports | 1 | August 11th, 2005 02:32 PM |
Reports with Date Range - Errors | jwr | Setting Up & Running Reports | 12 | August 8th, 2005 12:57 PM |
How do I query for a date range? | Kingster | General Discussion | 6 | April 20th, 2005 06:07 PM |
Query for 'confirmation' | rogge | Running & Setting Up Queries | 8 | April 19th, 2005 03:26 PM |
Date Range in Chart? | JSHWEH | Setting Up & Running Reports | 0 | February 2nd, 2005 08:17 PM |