Date Range filter for report

[JSimoncelli]

Hi All,

I have an access 2013 database, part of the database is used to track employee training courses. Some courses have an annual requirement. I have created a report that is based on a query that returns the information about a given course.

The data the query pulls is employee name, course ID, course name and course completed date. I have added criteria in the query that asks for the Course ID to filter on a given course and a dynamic field in the query AnnualReqDate: DateAdd("d",+365,[TrainingCourseCompleted]). What this returns is a list of employees that have completed course X the date they completed and the date (12 months) when the course is due. This works great, have created a report that reflects this very well.

The issue comes in when I try to add the ability to filter by date range on the AnnualReqDate dynamic field. I believe (and correct me if I am wrong), because the AnnualReqDate dynamic field does not exist until the query is run the Between [Start Date] And [End Date] criteria add to the AnnualReqDate dynamic field won’t work.

The query fires off but returns zero records.

Can anyone confirm this and make any suggestions.

John

 

[Ranman256]

You could ask a 2nd query based on the one in your example, Q1.
Q2 =
select * from Q1 where [AnnualReqDate] between forms!frmRpt!txtStartDate and forms!frmRpt!txtEndDate

 

[spikepl]

correct me if I am wrong

You are wrong.

Show the SQL.

 

[vbaInet]

You could ask a 2nd query based on the one in your example, Q1.
Q2 =
select * from Q1 where [AnnualReqDate] between forms!frmRpt!txtStartDate and forms!frmRpt!txtEndDate

Or using Ranman256's suggestion you could put the same criteria in the same query but in this format:

SELECT *
FROM Query
WHERE  DateAdd("d",+365,[TrainingCourseCompleted]) BETWEEN [[COLOR="blue"]Start Date[/COLOR]] AND [[COLOR="blue"]End Date[/COLOR]]

If you're going to use a separate form to grab the start and end date criteria like Ranman256 hinted, then you can reference the textboxes on the form in this format:

SELECT *
FROM Query
WHERE  DateAdd("d",+365,[TrainingCourseCompleted]) BETWEEN [Forms]![FormName]![[COLOR="Blue"]txtStartDate[/COLOR]] AND [Forms]![FormName]![[COLOR="blue"]txtEndDate[/COLOR]]

... but remember that the form must remain open whilst the report is opened too - you can toggle the visible property. There are other ways around this but I'm trying to keep things simple.

 

[JSimoncelli]

Thanks all for your responses,

@vBaInet,

I tried adding the BETWEEN statement to the query as you suggested, I could not get it to work.

Referencing using a separate Form to perform the date range function I found this Tip http://allenbrowne.com/casu-08.html

I followed the steps and got it to work with the exception of the Start Date. It filters the End Date and displays that date on the report. But the Start Date portion is not working. I can input 06/01/2015 and 06/30/2015 with the intent of viewing only records in June, however it displays from 06/30/2015 back including dates prior the June.

Can you take a look at the code, I can't seem to see any issues. I have very little experience with VBA code. I did change the variables needed for my situation like the report name..


John

 

[JSimoncelli]

@spikepl

Here is the sql code.

SELECT tblEmployee.Employee_ID, [LastName] & ", " & [FirstName] AS Employee, tblTrainingCourse.TrainingCourse_ID, tblTrainingCourse.TrainingCourseName, tblTrainingCourse.TrainingCourseType, tblTrainingCourseLink.TrainingCourseCompleted, tblTrainingCourseLink.TrainingCourseNote, tblTrainingCourseLink.TrainingCourseCertificate, tblTrainingCourse.AnnualReq, DateAdd("d",+365,[TrainingCourseCompleted]) AS AnnualReqDate
FROM tblTrainingCourse INNER JOIN (tblEmployee INNER JOIN tblTrainingCourseLink ON tblEmployee.Employee_ID = tblTrainingCourseLink.Employee_FK) ON tblTrainingCourse.TrainingCourse_ID = tblTrainingCourseLink.TrainingCourse_FK
WHERE (((tblTrainingCourse.TrainingCourse_ID) Like [Type Course ID]))
ORDER BY tblTrainingCourseLink.TrainingCourseCompleted, DateAdd("d",+365,[TrainingCourseCompleted]);

 

[spikepl]

There is no date restriction in this SQL. Floow what vbaInet suggested. Do not say "i did" but SHOW what you did, otherwise we have no clue.

What locale is set on your machine? What you described could fit with 6/1/2015 being perceived as Jan 6.

What happens if your range is 6/13/2015-6/30/2015?
What happens if your range is 6/1/2015-6/11/2105?

"I can input 06/01/2015 and 06/30/2015 " where exactly do you type this information?

 

[JSimoncelli]

@spikepl, my apologies.

You asked for the sql code prior to vbsInet's response. The sql code I sent was without his suggestion. Below in the sql code for the query with vbaInt's suggestion.

SELECT tblEmployee.Employee_ID, [LastName] & ", " & [FirstName] AS Employee, tblTrainingCourse.TrainingCourse_ID, tblTrainingCourse.TrainingCourseName, tblTrainingCourse.TrainingCourseType, tblTrainingCourseLink.TrainingCourseCompleted, tblTrainingCourseLink.TrainingCourseNote, tblTrainingCourseLink.TrainingCourseCertificate, tblTrainingCourse.AnnualReq, DateAdd("d",+365,[TrainingCourseCompleted]) Between [Start Date] And [End Date] AS AnnualReqDate
FROM tblTrainingCourse INNER JOIN (tblEmployee INNER JOIN tblTrainingCourseLink ON tblEmployee.Employee_ID = tblTrainingCourseLink.Employee_FK) ON tblTrainingCourse.TrainingCourse_ID = tblTrainingCourseLink.TrainingCourse_FK
WHERE (((tblTrainingCourse.TrainingCourse_ID) Like [Type Course ID]))
ORDER BY tblTrainingCourseLink.TrainingCourseCompleted, DateAdd("d",+365,[TrainingCourseCompleted]) Between [Start Date] And [End Date];

When I run the query with this code it returns 0's in the AnnualReqDate field.

John

 

[spikepl]

The way this works is that we have ideas and ask questions to verify or dispell them.

If you do not wish to play along by actually answering all the questions asked that's no skin off my nose. Someone else will be better able to help you.

 

[vbaInet]

In this case it would be much easier to see your db. Upload a cut-down version of your db with some (non-confidential) sample data and include only the relevant tables and queries, and I'll take a look.

I think that it could be one of the following:
1. The criteria is working but you're expecting data where there shouldn't be data
2. It's not working because date needs to be properly formatted
3. It's not working because of how everything else is setup.

 

[JSimoncelli]

@vbaInet,

I am having issues with uploading the db, I have done this in the past..

May be the size but the error wants me to contact the admin..

I did however get the vba code I posted earlier working...

John.

 

[vbaInet]

Good to hear JSimoncelli!

Perhaps the db was too big. Next time try zipping it before uploading.

What exactly was the error message?

 

[JSimoncelli]

@vbaInet

Here is a chopped down version, cut over half of the employees. The employee select part on the main page does not work, cut that part out. I do have it set up to break the employee’s up into the four groups by last name.

The qryTrainingCourseAnnualReqDate is the query I was originally asking about. My original question still remains. In the query there is a dynamic field created when the query runs AnnualReqDate: DateAdd("d",+365,[TrainingCourseCompleted]) . Because of this I can’t seem to put in a Between Date criteria for this field. I believe this is because the date does not exist until the query is completed.

I have tried yours and the others suggestion’s but couldn’t get them to work. If you can please let me know where I went wrong.

The current Training Course with Annual Training Requirement report using the vba code associated with the Run Report button does work. It was from a TIP back in 2009.

I am self-thought and have used this forum often. The database may be a little clunky but not too bad for a rookie.

On a side note, from the Training Course/Event Reports and Management page check out the Create/Edit/View Training Event section. The Attendance process is my shot at getting employees updated as a group rather than having to enter the data one employee at a time. The reason I bring this up is, it was pointed out that I should not use a table as a “temporary table”. But it works, if there is a better way please let me know.

One more thing to help understand, Courses are taken by individual employees whereas Events are attended by 2 or more employees, hence the reason for the separation of the training types.

John

 

[vbaInet]

Your main problem was that you hadn't defined the parameters in the Parameters dialog box. This was explained in Allen Browne's link.

In the attached, have a look at:
1. the query - note that I've had to remove the TrainingID parameter for testing purposes
2. the Parameters list - there is a button next to the Totals button called Parameters, click that and it will open up a new dialog and this is where you define any type of parameters
3. The code behind the button on the form

A criteria of 01/06/2015 and 18/06/2015 yields 3 results with the right dates. You may continue to use the code method (because it offers more flexibility) but this is just to show you how you can achieve the same without code.

On a side note, from the Training Course/Event Reports and Management page check out the Create/Edit/View Training Event section.

I'm not sure which forms, tables or queries you're referring to here. And if this is a much bigger question it's probably best to post a new thread.

For next time, when you're posting a cut-down version, please only include the relevant tables, queries, forms and reports. It's a tedious task wading through objects trying to find the right one, especially when most of the object names are similar.

Upload a cut-down version of your db with some (non-confidential) sample data and include only the relevant tables and queries...

 

[JSimoncelli]

Thank you so much..

Again my apologies to all, if didn't follow you advice. It was due to more not knowing what you were asking than anything ells. Like I said I am a rookie.

I didn't know about the Parameters button.

I think I can stumble through it from here..

I will make a different post concerning the table..

Once again thanks all..

John

 

[vbaInet]

Not a problem at all, I can understand when you're learning. If there's anything you don't understand or need clarity on, just ask and we'll advise.

By the way, you could consider adding that TrainingID parameter on your form and use that form for entering any kind of criteria for your report.

Good luck with it!