DateAdd Function
Home > IT Answers > Database > DateAdd Function
AccessFlunky
50 pts.
0
Q:
DateAdd Function
DateAdd Function, Access 2007 functions, Microsoft Access, Access 2007
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?
ASKED: May 5 2009  4:38 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Carlosdl
29855 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
Can you post your query ?

I guess you are comparing [Enter Date] to [StartDate] and not to DateAdd("yyyy",[Length],[StartDate])

-----------------------------
Last Answered: May 5 2009  4:56 PM GMT by Carlosdl   29855 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

AccessFlunky   50 pts.  |   May 5 2009  5:04PM GMT

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?

 

Carlosdl   29855 pts.  |   May 12 2009  2:01PM GMT

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.

 

AccessFlunky   50 pts.  |   May 12 2009  2:11PM GMT

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!

 
0