DateAdd Function
I have a dateadd function in a query Cdate:DateAdd("yyyy",[Length],[StartDate]). I need the year added from how long we give someone to complete certain courses. Example, Lenght = 2, StartDate = 1/2/2007, Cdate = 1/2/2009. This works and that's not the problem. Now I need to run the query with the criteria stating <[Enter Date]. This is to retrieve all dates less than a given date. We may run this query every 6 months to see who needs courses to complete their certificate. This should work, but when I run the query, the dates aren't less than the Date Entered. Why?
Software/Hardware used:
Software/Hardware used:
ASKED:
May 5, 2009 4:38 PM
UPDATED:
May 12, 2009 2:11 PM
RELATED QUESTIONS
- Crystal Reports- displaying calculations for date range prior to date search parameter
- and IIf statement in a query
- VB Script to Create a Scheduled Task on a Remote Machine Using Alternate Credentials
- VB Script to monitor files on Windows machines from a Zenoss server
- Microsoft Access Query Questions
Answer Wiki:
Can you post your query ?
<b><i>I guess</i></b> you are comparing [Enter Date] to [StartDate] and not to DateAdd("yyyy",[Length],[StartDate])
-----------------------------
To see all answers submitted to the Answer Wiki: View Answer History.
SELECT Student.ID AS Student_ID, Student.StudentID, Student.FirstName, Student.LastName, Student.Address, Student.Address2, Student.City, Student.State, Student.Zip, Student.PhoneNumber, Student.MobileNumber, Student.EmailAddress, Student.StartDate, Course.CourseNumber, Course.CourseName, Course.Required, Course.Certificate_ID, Certificate.ID AS Certificate_ID, Certificate.Certificate, Certificate.Length, DateAdd(“yyyy”,[Length],[StartDate]) AS CompletionDate
FROM (Certificate INNER JOIN Course ON Certificate.ID = Course.Certificate_ID) INNER JOIN (Student INNER JOIN Table1 ON Student.ID = Table1.Student_ID) ON Course.CourseNumber = Table1.CourseNumber
WHERE (((DateAdd(“yyyy”,[Length],[StartDate]))<[Enter Date]))
ORDER BY DateAdd(“yyyy”,[Length],[StartDate]) DESC;
Does this help?
Sorry, I don’t have an answer. It should work.
I have read in the web some other people having similar problems. It seems to work when you use the DateAdd function in the WHERE clause with static values, but fails when using column names or parameters.
What I had to do to get it to work was just query the DateAdd function then query that value back to another query to run the <=[Cdate] criteria! Now it works and I get the information that I need. But thanks so much for your help!