|
tech
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How do I creat a Date Range to open ReportI have a report named ( Pay Hours ). How can I open this report based on
start date and end date. Thanks in Advance You can add parameters to the query that the report's based on that'll
prompt you for the dates, or you can create a form where you supply the dates before you call the report (again, the query would point to the form field as parameters), or you can put code in the report's Open event that prompts for the dates, and then set a filter for your report. Post back if you need help with any of those solutions (make sure you indicate which one you want to use...) -- Show quoteDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Alvin Smith" <AlvinSm***@discussions.microsoft.com> wrote in message news:C6189823-68EA-4DAF-8A56-1BFA578A3E5C@microsoft.com... >I have a report named ( Pay Hours ). How can I open this report based on > start date and end date. Thanks in Advance I much prefer the form method that Doug mentions. This page
http://www.fontstuff.com/access/acctut08.htm has some examples of how to implement this. Form controls are much better than parameter prompts since: -You can see all criteria values at one time -Criteria values can be re-used without re-keying -You can check for integrity -You can use controls like combo and list boxes, option groups... -You can set defaults -You can run code in the form -You can use input masks -(other stuff) -- Show quoteDuane Hookom MS Access MVP "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message news:usoN8pAHFHA.3968@TK2MSFTNGP14.phx.gbl... > You can add parameters to the query that the report's based on that'll > prompt you for the dates, or you can create a form where you supply the > dates before you call the report (again, the query would point to the form > field as parameters), or you can put code in the report's Open event that > prompts for the dates, and then set a filter for your report. > > Post back if you need help with any of those solutions (make sure you > indicate which one you want to use...) > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no e-mails, please!) > > > > "Alvin Smith" <AlvinSm***@discussions.microsoft.com> wrote in message > news:C6189823-68EA-4DAF-8A56-1BFA578A3E5C@microsoft.com... >>I have a report named ( Pay Hours ). How can I open this report based on >> start date and end date. Thanks in Advance > > My Query is working great. But! I have created a form called Form1 which has
two unbound text boxes. Where in the Query do I call this form for the input? Do I put WHERE (((Forms!Form1.BillingDate) Between [BeginingDate] And [EndingDate])) in criteria of the BillingDate? If so it gives me this Error; undefined function 'Where' in expression. Thanks for the help! Show quote "Duane Hookom" wrote: > I much prefer the form method that Doug mentions. This page > http://www.fontstuff.com/access/acctut08.htm has some examples of how to > implement this. Form controls are much better than parameter prompts since: > -You can see all criteria values at one time > -Criteria values can be re-used without re-keying > -You can check for integrity > -You can use controls like combo and list boxes, option groups... > -You can set defaults > -You can run code in the form > -You can use input masks > -(other stuff) > > -- > Duane Hookom > MS Access MVP > > > "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message > news:usoN8pAHFHA.3968@TK2MSFTNGP14.phx.gbl... > > You can add parameters to the query that the report's based on that'll > > prompt you for the dates, or you can create a form where you supply the > > dates before you call the report (again, the query would point to the form > > field as parameters), or you can put code in the report's Open event that > > prompts for the dates, and then set a filter for your report. > > > > Post back if you need help with any of those solutions (make sure you > > indicate which one you want to use...) > > > > -- > > Doug Steele, Microsoft Access MVP > > http://I.Am/DougSteele > > (no e-mails, please!) > > > > > > > > "Alvin Smith" <AlvinSm***@discussions.microsoft.com> wrote in message > > news:C6189823-68EA-4DAF-8A56-1BFA578A3E5C@microsoft.com... > >>I have a report named ( Pay Hours ). How can I open this report based on > >> start date and end date. Thanks in Advance > > > > > > > I would have expected that Form1 would have text boxes txtBeginningDate and
txtEndingDate on it. If you're working with your query in the graphical designed, you'd put Between CDate(Forms!Form1.txtBeginningDate) And CDate(Forms!Form1.txtEndingDate) in the criteria row under the BillingDate field. -- Show quoteDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Alvin Smith" <AlvinSm***@discussions.microsoft.com> wrote in message news:453C40EC-C129-46C2-AD9C-9A6BBC8C2BCF@microsoft.com... > My Query is working great. But! I have created a form called Form1 which > has > two unbound text boxes. Where in the Query do I call this form for the > input? > Do I put > WHERE (((Forms!Form1.BillingDate) Between [BeginingDate] And > [EndingDate])) > in criteria of the BillingDate? If so it gives me this Error; > undefined function 'Where' in expression. > Thanks for the help! > > > > "Duane Hookom" wrote: > >> I much prefer the form method that Doug mentions. This page >> http://www.fontstuff.com/access/acctut08.htm has some examples of how to >> implement this. Form controls are much better than parameter prompts >> since: >> -You can see all criteria values at one time >> -Criteria values can be re-used without re-keying >> -You can check for integrity >> -You can use controls like combo and list boxes, option groups... >> -You can set defaults >> -You can run code in the form >> -You can use input masks >> -(other stuff) >> >> -- >> Duane Hookom >> MS Access MVP >> >> >> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message >> news:usoN8pAHFHA.3968@TK2MSFTNGP14.phx.gbl... >> > You can add parameters to the query that the report's based on that'll >> > prompt you for the dates, or you can create a form where you supply the >> > dates before you call the report (again, the query would point to the >> > form >> > field as parameters), or you can put code in the report's Open event >> > that >> > prompts for the dates, and then set a filter for your report. >> > >> > Post back if you need help with any of those solutions (make sure you >> > indicate which one you want to use...) >> > >> > -- >> > Doug Steele, Microsoft Access MVP >> > http://I.Am/DougSteele >> > (no e-mails, please!) >> > >> > >> > >> > "Alvin Smith" <AlvinSm***@discussions.microsoft.com> wrote in message >> > news:C6189823-68EA-4DAF-8A56-1BFA578A3E5C@microsoft.com... >> >>I have a report named ( Pay Hours ). How can I open this report based >> >>on >> >> start date and end date. Thanks in Advance >> > >> > >> >> >> Please explain how to add parameters to a report which will prompt me for
dates when the report is opened. Read everything - guess I am a little slow. Keep it as simple as possible please! Thanks! Show quote "Douglas J. Steele" wrote: > You can add parameters to the query that the report's based on that'll > prompt you for the dates, or you can create a form where you supply the > dates before you call the report (again, the query would point to the form > field as parameters), or you can put code in the report's Open event that > prompts for the dates, and then set a filter for your report. > > Post back if you need help with any of those solutions (make sure you > indicate which one you want to use...) > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no e-mails, please!) > > > > "Alvin Smith" <AlvinSm***@discussions.microsoft.com> wrote in message > news:C6189823-68EA-4DAF-8A56-1BFA578A3E5C@microsoft.com... > >I have a report named ( Pay Hours ). How can I open this report based on > > start date and end date. Thanks in Advance > > > Patrick Stansbury wrote:
> Please explain how to add parameters to a report which will prompt me for In the Query which the Report uses as its data source, add a reference > dates when the report is opened. Read everything - guess I am a little slow. > Keep it as simple as possible please! > > Thanks! to some field that you did not define anywhere. For example, suppose your underlying Table contains these data: [T_Transactions] Customer Servicing date amount Name branch -------- --------- ----- ------ Jim c 9/13 $15.00 Jim c 9/13 $18.00 Mary c 9/10 $25.00 Define a Query thus: SELECT T_Transactions.[Customer Name], T_Transactions.[Servicing branch], T_Transactions.date, T_Transactions.amount FROM T_Transactions WHERE (((T_Transactions.date)=[MyDate])); and run it; a message box will pop up asking you for a value for [MyDate]. If you enter "9/13", you should get this: Customer Servicing date amount Name branch -------- --------- ----- ------ Jim c 9/13 $15.00 Jim c 9/13 $18.00 Be aware that there are fancier ways to do this, such as defining your own Text Box, but this should do what you need for now. -- Vincent Johns <vjo***@alumni.caltech.edu> Please feel free to quote anything I say here. Show quote > "Douglas J. Steele" wrote: > > >>You can add parameters to the query that the report's based on that'll >>prompt you for the dates, or you can create a form where you supply the >>dates before you call the report (again, the query would point to the form >>field as parameters), or you can put code in the report's Open event that >>prompts for the dates, and then set a filter for your report. >> >>Post back if you need help with any of those solutions (make sure you >>indicate which one you want to use...) >> >>-- >>Doug Steele, Microsoft Access MVP >>http://I.Am/DougSteele >>(no e-mails, please!) >> >> >> >>"Alvin Smith" <AlvinSm***@discussions.microsoft.com> wrote in message >>news:C6189823-68EA-4DAF-8A56-1BFA578A3E5C@microsoft.com... >> >>>I have a report named ( Pay Hours ). How can I open this report based on >>>start date and end date. Thanks in Advance >> >> >> Thaanks Vincent,
But I am a little too inexperienced to understand what you wrote... What I have is a sales report that includes records like INVOICE DATE and CLIENT and AMOUNT and AD SIZE and more. The report works FINE - but it includes every record in my database. What I want to happen is that the report will ask for a date range - using the dates in my INVOICE DATE field (09-15-2005 is how it is expressed) to generate the report. I need to be able to do a single day - a week or a entire month. It seems it should be easy - but I have been sitting here for hours and can not figure out how to do it! Can you offer help? Thanks! Show quote "Vincent Johns" wrote: > Patrick Stansbury wrote: > > > Please explain how to add parameters to a report which will prompt me for > > dates when the report is opened. Read everything - guess I am a little slow. > > Keep it as simple as possible please! > > > > Thanks! > > > In the Query which the Report uses as its data source, add a reference > to some field that you did not define anywhere. For example, suppose > your underlying Table contains these data: > > [T_Transactions] > > Customer Servicing date amount > Name branch > -------- --------- ----- ------ > Jim c 9/13 $15.00 > Jim c 9/13 $18.00 > Mary c 9/10 $25.00 > > Define a Query thus: > > SELECT T_Transactions.[Customer Name], > T_Transactions.[Servicing branch], > T_Transactions.date, T_Transactions.amount > FROM T_Transactions > WHERE (((T_Transactions.date)=[MyDate])); > > and run it; a message box will pop up asking you for a value for > [MyDate]. If you enter "9/13", you should get this: > > Customer Servicing date amount > Name branch > -------- --------- ----- ------ > Jim c 9/13 $15.00 > Jim c 9/13 $18.00 > > Be aware that there are fancier ways to do this, such as defining your > own Text Box, but this should do what you need for now. > > -- Vincent Johns <vjo***@alumni.caltech.edu> > Please feel free to quote anything I say here. > > > > "Douglas J. Steele" wrote: > > > > > >>You can add parameters to the query that the report's based on that'll > >>prompt you for the dates, or you can create a form where you supply the > >>dates before you call the report (again, the query would point to the form > >>field as parameters), or you can put code in the report's Open event that > >>prompts for the dates, and then set a filter for your report. > >> > >>Post back if you need help with any of those solutions (make sure you > >>indicate which one you want to use...) > >> > >>-- > >>Doug Steele, Microsoft Access MVP > >>http://I.Am/DougSteele > >>(no e-mails, please!) > >> > >> > >> > >>"Alvin Smith" <AlvinSm***@discussions.microsoft.com> wrote in message > >>news:C6189823-68EA-4DAF-8A56-1BFA578A3E5C@microsoft.com... > >> > >>>I have a report named ( Pay Hours ). How can I open this report based on > >>>start date and end date. Thanks in Advance > >> > >> > >> > Patrick Stansbury wrote:
Show quote > Thaanks Vincent, OK, if you want to use a date range, then you can use a Query like the > > But I am a little too inexperienced to understand what you wrote... What I > have is a sales report that includes records like INVOICE DATE and CLIENT and > AMOUNT and AD SIZE and more. The report works FINE - but it includes every > record in my database. What I want to happen is that the report will ask for > a date range - using the dates in my INVOICE DATE field (09-15-2005 is how it > is expressed) to generate the report. I need to be able to do a single day - > a week or a entire month. It seems it should be easy - but I have been > sitting here for hours and can not figure out how to do it! > > Can you offer help? > > Thanks! one I suggested, except with two dates, something like this: ... WHERE (((T_Transactions.date)>=[StartDate]) AND ((T_Transactions.date)<=[EndDate])); If you know that the value of your parameter [EndDate] is always going to be yesterday, or last Saturday, then you can compute that with a function and not have the user type in a date. The query would then contain something like this, in which Now() is the function referring to the current moment, though you'd probably want to use some variation on that: ... WHERE (((T_Transactions.date)>=[StartDate]) AND ((T_Transactions.date)<=Now())); If you've tried using the Query I suggested earlier, did that work? If not, what happened when you tried using it? -- Vincent Johns <vjo***@alumni.caltech.edu> Please feel free to quote anything I say here. Show quote > "Vincent Johns" wrote: > > >>Patrick Stansbury wrote: >> >> >>>Please explain how to add parameters to a report which will prompt me for >>>dates when the report is opened. Read everything - guess I am a little slow. >>> Keep it as simple as possible please! >>> >>>Thanks! >> >> >>In the Query which the Report uses as its data source, add a reference >>to some field that you did not define anywhere. For example, suppose >>your underlying Table contains these data: >> >>[T_Transactions] >> >>Customer Servicing date amount >>Name branch >>-------- --------- ----- ------ >>Jim c 9/13 $15.00 >>Jim c 9/13 $18.00 >>Mary c 9/10 $25.00 >> >>Define a Query thus: >> >> SELECT T_Transactions.[Customer Name], >> T_Transactions.[Servicing branch], >> T_Transactions.date, T_Transactions.amount >> FROM T_Transactions >> WHERE (((T_Transactions.date)=[MyDate])); >> >>and run it; a message box will pop up asking you for a value for >>[MyDate]. If you enter "9/13", you should get this: >> >>Customer Servicing date amount >>Name branch >>-------- --------- ----- ------ >>Jim c 9/13 $15.00 >>Jim c 9/13 $18.00 >> >>Be aware that there are fancier ways to do this, such as defining your >>own Text Box, but this should do what you need for now. >> >> -- Vincent Johns <vjo***@alumni.caltech.edu> >> Please feel free to quote anything I say here. Mr. Steele, in beginners terms - how do I add parameters to a report that
will prompt it to ask for dates? I added the parameter - it asked for the dates - but the report was unchanged. It still listed all data rather than a specific date. One expression in my database is INVOICE DATE. I want to be able to generate areport by day, week or month... Thanks for your consideration! Show quote "Douglas J. Steele" wrote: > You can add parameters to the query that the report's based on that'll > prompt you for the dates, or you can create a form where you supply the > dates before you call the report (again, the query would point to the form > field as parameters), or you can put code in the report's Open event that > prompts for the dates, and then set a filter for your report. > > Post back if you need help with any of those solutions (make sure you > indicate which one you want to use...) > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no e-mails, please!) > > > > "Alvin Smith" <AlvinSm***@discussions.microsoft.com> wrote in message > news:C6189823-68EA-4DAF-8A56-1BFA578A3E5C@microsoft.com... > >I have a report named ( Pay Hours ). How can I open this report based on > > start date and end date. Thanks in Advance > > > On Thu, 15 Sep 2005 15:37:03 -0700, "Patrick Stansbury"
<PatrickStansb***@discussions.microsoft.com> wrote: >Mr. Steele, in beginners terms - how do I add parameters to a report that You don't add parameters to a Report.>will prompt it to ask for dates? I added the parameter - it asked for the >dates - but the report was unchanged. It still listed all data rather than a >specific date. One expression in my database is INVOICE DATE. I want to be >able to generate areport by day, week or month... You add parameters to a Query, and *base the Report on the Query*. It sounds like the Report is based on your table. Open the report in design view, and - if you have the query Douglas suggested - change the Record Source property of the Report from the name of your table to the name of that query. John W. Vinson[MVP]  |
|||||||||||||||||||||||
Other interesting topics