Making a query with user selectable date ranges 2

[PaveFE]

Hi all,
I found this site while googling, and have a question.
I'm in the Air Force and have built a database using MS Access for our missin completion (I fly on helicopters) and to keep track of our student's progress through the training course we teach here.

My question is, how can I make a form (more so a query) so that the user inputs a date range, whether it's a week a month, etc., and pulls the information they need and have it print to a report. I have all my tables done with the students' info and mission info. But how can I set up a query for them to get the "stats" of this info?

I can provide more details if necessary and even send the database. I'm hopefully going to attend New Horizons at the end of July to get the MS Access Certification, but you never know with the Air Force.

Thanks in advance,

VD

 

[melaniecarr23]

If your query, you could use the flight_date field or whatever field you would like to query on to prompt the user for starting and ending dates.

Example

field flight_date
criteria could be between ["Starting date"] and ["End date"]

Hope this helps!
Melanie

 

[FancyPrairie]

I think you will eventually find that you will want to do more than just a date range search. Regardless, I would create a form (your Report Criteria Form) and place 2 fields on the form (Begin Date and End Date). (You can eventually add more fields (i.e. multi-select list box from which to select individual names in addition to the date range or excluding the date ranges). Then check out this FAQ faq181-5497. It will loop thru all of the controls on your form and return the Where clause for you. It works on date ranges, single and multi-select list boxes, combo boxes, text boxes, option groups, and check boxes. You only have to do 3 things to make it work.

1. Create a new module and copy and paste the function from the FAQ into the new module.
2. Set the tag properties of your begin and end date controls as specified in the FAQ.
3. Open your report as specified in the FAQ (i.e. DoCmd.OpenReport "YourReport",,,BuildWhere(Me)

Note, you don't have to worry about how the code works, just use it. Also, setting the tag property correctly is the key to making it work.

 

[PaveFE]

Okay, I'm willing to sound silly, and I guess I should have mentioned this earlier, I'm not entirely smart on Access, just enough to make tables, forms, reports, and a few functions.
With that being said, how do I make a module? Do you mean a text box on a form?
You are correct, I will do more than just a date range search, I will also want to do one by student (so we can look at individual progress) as well as other criteria such as how many missions were effective, and if not why, etc.
The information you gave me sounds like just what I need, but I'm lost when you say place the fields on the form, fields from where?
Sorry for asking extra help, but I very much appreciate it.

VD

 

[PHV]

Go to VBE (Alt+F11), menu Insert -> Module

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[FancyPrairie]

Start by creating a new module as PHV indicated. Then copy and paste the function from the FAQ into the new module. Close the module and name it something like "basBuildWhere". Next, create a new form and add 2 text boxes from which the user will enter the date range. The name of the first text box (start date) must end with "_BeginR" (i.e. txtMission_BeginR). The name of the 2nd text box must end with "_EndR" and have the same prefix as the 1st text box (i.e. txtMission_EndR).

Now, suppose you have a report whose Recordsource is set to a query that already has the date range hard coded in it. For example, the query might look like this: Select * from tblMission Where dtmMission Between #4/1/2005# and #6/30/2005#.

The tag property of the 1st text box would then look like this: Where=tblMission.dtmMission,Date;

The query for the report would then not need the where clause and would look like this: Select * From tblMission;

If you want to select one or more individuals to be included in your report, then place a multi-select list box on your form and set its Tag property appropriately (i.e. Where=tblMission.lngStudentID,Long. Your list box might contain 2 columns; StudentID (lngStudentID) and Student Name (strStudentName). Note that the first column would probably be hidden form the user (0 length).

Before you add too many controls on your form, make sure your date range works (i.e tag property is set correctly). To test the date range, place a command button on your form and in the Onclick event, include something like this: Msgbox BuildWhere Me. Then enter a start and begin date and click on the command button. You should see the results returned from BuildWhere

 

[PaveFE]

Sorry to sound like an idiot, but hey, if the shoe fits right?
Anyways, thanks for your help, I will give this a go and let you know how it works out.

VD

 

[PaveFE]

Do I set the Tag properties of the text boxes (start and end date) under the Other tab when I open the properties of the box?
I will want to do this for the list box as well so that I can choose a student and get his records for the specified date range. But I will definitely heed your advice and make sure the date ranges work first.

Thanks,

VD

 

[FancyPrairie]

Yes, select either the Other Tab or the All Tab to view/edit the Tag Property.