How do I get my query to pull data based on two different sets of date parameters?

[Emily Crawford]

I have a query that has two date fields. One date field is from the Request Table and it is called RequestDate - the date the employee requested training. The second date field is called ClassTaken from the Enrollment Table - and it is the day the employee took the training they requested. I am trying to create a report that asks the user to input one set of dates for the RequestDate field and a different set of dates for the ClassTaken date fields at the same time. I have the first parameters working fine. I used Between [Enter Start Date] and [Enter End Date] in the query under the RequestDate field. I tried the same thing under the ClassTaken field but it does not work for the second set of criteria. I also tried creating two separate queries and some other things but I am stumped and getting more confused now with all these 'practice' queries all over my database. Any help would be appreciated. Thanks.

[dsatino]

Can you post the SQL of your original query please.

[Emily Crawford]

SELECT [PracticeQuery2 FOR USE IN ENROLLMENT QUERY].EmployeeLastNa me, [PracticeQuery2 FOR USE IN ENROLLMENT QUERY].EmployeeFirstN ame, [PracticeQuery2 FOR USE IN ENROLLMENT QUERY].RequestDate, [PracticeQuery2 FOR USE IN ENROLLMENT QUERY].EmployeeNum, [PracticeQuery2 FOR USE IN ENROLLMENT QUERY].TrainingReques ts, Enrollment2.Emp loyeeNum, Enrollment2.Tra iningTopicTaken , Enrollment2.Dat eTaken
FROM [PracticeQuery2 FOR USE IN ENROLLMENT QUERY] LEFT JOIN Enrollment2 ON ([PracticeQuery2 FOR USE IN ENROLLMENT QUERY].TrainingReques ts = Enrollment2.Tra iningTopicTaken ) AND ([PracticeQuery2 FOR USE IN ENROLLMENT QUERY].EmployeeNum = Enrollment2.Emp loyeeNum)
WHERE ((([PracticeQuery2 FOR USE IN ENROLLMENT QUERY].RequestDate) Between [Enter Start Date] And [Enter End Date]) AND ((Enrollment2.D ateTaken) Between [Enter Start Date] And [Enter End Date]));


When I delete the second [Enter Start Date] And [Enter End Date] it appears to pull the first set of data properly. I probably did all of it completely wrong.

[dsatino]

I think your problem is that you think the [Enter Start Date] and [Enter End Date] syntax is some sort of pre-defined function. They're not. Basically, when you put the [] in the criteria field of the query you're telling the query to prompt the user for the necessary criteria. The text that falls between the [] is completely up to you.

By using the same exact text between the [] for both criteria, you are capturing the dates for request dates and then overwriting them with DateTaken dates. I'm actually surprised it's asking twice.

In any case, you should be fine if you just change the prompt text. i.e. [Enter Start Date for Request], [Enter End Date for Request], [Enter Start Date for Taken], [Enter End Date for Taken].

This should solve your problem as long as your SQL is good.

[Emily Crawford]

Okay, thank you. I'll try.