How to make Access asks for date range? 1

[hawkeye71]

Hi Folks:
I am writing a query in which I need to specify a date range, e.g. Start_Date (1/1/2004) and End_Date (1/11/2004).
When I use the 'Between #1/1/2004# and #1/11/2004#' in the criteria field, I get the desired results.

I want to make the Criteria so that when I run the query it asks me to enter the Start_Date and End_Date, instead of me specifying the date range in the QBE grid?

Thanks,
Indiana

 

[Blorf]

In the criteria field, put this

>=[Enter Start Date] and <=[Enter Ending Date]

This will ask for the dates when the query is run.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[gearhead03]

If you would like it to look a little nicer you could make a small pop-up form. Call it DateRange Put 2 unbound text boxes on it call one StartDate and the other EndDate. Set the format to short date and input mask to short date. Add the following line in your query in the date column. Between forms.DateRange.StartDate and Forms.DateRange.EndDate. Place a Button to open the report on your pop-up form. Enter the Dates, click the button and there you have it. If you do not want to close the pop-up form everytime, Make a Macro to; 1)Open the Report 2)Close the aforementioned form. Then make you button run the Macro instead of just opening the report. Hope this helps.
Mark

 

[hfriesen]

Along the same lines of this question, here is my question:

I am at a training institute where courses are scheduled throughout the calendar year. Some courses are 6 weeks in duration, some are 5 weeks, some are 1 week. Our cafeteria needs to know on a week to week basis how many students will be eating in the cafeteria. A report has been created to provide them with this information only it does not pick up a course which starts on a Tuesday, for example. It will only pick up courses that run the full 5 days per week.

I'm trying to add this to my query, but everything I've tried so far does not work.

I'll give you an example:

In my report, I want to know which courses and how many students will be here the week of March 8 to 12, 2004. There are 3 courses running during this week. One starts Feb. 16, 2004 and ends March 26, 2004, another course is running on those exact dates, and a third is running Feb. 16, 2004 to March 19, 2004. So it should pick up all 3 courses. Additionally, one 3-day course is running March 9 to 11, 2004. It'll pick up the first 3 courses, but not the 3-day course.

The start date and end date is entered in separate cells of the table which holds the course dates. On the user form used to generate the weekly report for the cafeteria, the weekly start date and end date are manually inputted for the week that they need a report for.

In my query, I've tried the following Criteria statement: <=[Forms]![frm_Meal_Plans]![txt_End] in my End Date field with nothing in my Start Date field. But this picks up courses which started at the beginning of the year and not this particular week in question. I've also tried a Between...And statement and that gave me incorrect results as well.

Anyway, I may have provided more information than necessary to answer my question. Any help would be greatly appreciated.

Thanks!

Heather

 

[gearhead03]

I am assuming that frm_meal_plans is the form that the user enters the date for the report.
You do not mention this option so I am going to throw it out there.
You actually need two criteria in your query.
in the start date field
>=[Forms]![frm_Meal_Plans]![txt_start]
txt_start is whatever the name of the text box that the user enters beginning date.
use the criteria for end date from above and that should fix it.
let me know