I have set-up a database to track phone calls that are made to students who aren't paying their bills. I want the report to only include phone calls made on a particular week, not all phone calls for all periods. I also need the report to dump old weeks and keep the current information. I do not want to lose the old phone calls off the tables when the report is generated. This is my first project and this is a stumbling block.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Creating report for only a certain time period. "newbie"
- Thread starter kristin2
- Start date
You have to test the call date in a query and then use the query as the basis for a report (or form, for that matter). You can set up a parameter query, so that you input the cut-off date in a screen and the have the report printed just for that date. search the help file for details on parameter queries.
- Thread starter
- #3
I have attemped to look at the parameter queries and I am still lost. This is truly my first project so all the help available is appreciated. I have tried to obtain help within the organization but have not received any. I am not truly a programmer by trade. I am helping to create this database for our financial aid office. Please help!!!!
Hi,
I think that I can help you, since I am doing alot of calculations based on a range of dates. Here is the strategy I used to get this to work:
1) I created a form where the user can select a range of dates. In your case, you can create a table containing valid weeks. For instance, this table might have a field called WeekStartDate, based on a Monday starting date: 12/16/2002 (the record before that might be 12/09/2002).
Let's call this listbox on your form to be lstWeekStartDate.
2) In your query, I presume that you have a date field that contains the date that the student was called. In the criteria box for that field, simply right click and select "Build". Scroll down and select "Forms", then select the form name, then select the listbox (lstWeekStartDate). The code inside the criteria box should be as follows:
Between [Forms]![frmYourForm]![lstWeekStartDate] and ([Forms]![frmYourForm]![lstWeekStartDate] + 7)
Essentially, you are using the date field from the table to select only those records that have a range of dates starting with the date selected by the user, plus 7 more days. Does this make sense?
Your form would then have a command button to run the report
HTH,
Randy Smith
California Teachers Association
I think that I can help you, since I am doing alot of calculations based on a range of dates. Here is the strategy I used to get this to work:
1) I created a form where the user can select a range of dates. In your case, you can create a table containing valid weeks. For instance, this table might have a field called WeekStartDate, based on a Monday starting date: 12/16/2002 (the record before that might be 12/09/2002).
Let's call this listbox on your form to be lstWeekStartDate.
2) In your query, I presume that you have a date field that contains the date that the student was called. In the criteria box for that field, simply right click and select "Build". Scroll down and select "Forms", then select the form name, then select the listbox (lstWeekStartDate). The code inside the criteria box should be as follows:
Between [Forms]![frmYourForm]![lstWeekStartDate] and ([Forms]![frmYourForm]![lstWeekStartDate] + 7)
Essentially, you are using the date field from the table to select only those records that have a range of dates starting with the date selected by the user, plus 7 more days. Does this make sense?
Your form would then have a command button to run the report
HTH,
Randy Smith
California Teachers Association
- Thread starter
- #5
Either I am a numskull or this is way above my head. I tried to do that exactly the way that you said. I keep getting an error message when I run the query that says This expression is is typed incorrectly or it is too complex to be evaluated. I added the field WeekStartDate into my table. Then I tried to create a form with a listbox and called it 1stWeekStartDate. Then I tried to code for the week and that is when I got the error message on the query.
Help!!
Thanks
Help!!
Thanks
Hi,
It looks like your code is using 1st(first), and not lst (as in list). Do you see the difference? Listboxes generally are named with the prefix of L-S-T (this should be lower case, as in "lst"
.
HTH,
Randy Smith
California Teachers Association
It looks like your code is using 1st(first), and not lst (as in list). Do you see the difference? Listboxes generally are named with the prefix of L-S-T (this should be lower case, as in "lst"
.HTH,
Randy Smith
California Teachers Association
- Thread starter
- #7
- Thread starter
- #8
I have the table set-up correctly with a WeekStartDate field. Then I have created a form with the date, first name, last name, phone number, results, and reference name for the individual called. Then I used the short-cut menu for a list-box. The dialog box opens to ask if I want the list box to look-up the values in a table or query, I will type in the values I want or Find a record on my form based on the value I selected in my list box. The first time I selected number one. Is that wrong?
Kristin,
You should have set up the list box to come from the table you created of valid date ranges. That way, only the entries you have created will be valid. Although you can create a list of values that you enter, it can be a pain to update regularly. A table, in my opinion, is much easier to update. HTH,
Randy Smith
California Teachers Association
You should have set up the list box to come from the table you created of valid date ranges. That way, only the entries you have created will be valid. Although you can create a list of values that you enter, it can be a pain to update regularly. A table, in my opinion, is much easier to update. HTH,
Randy Smith
California Teachers Association
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question