DateAdd Function

50 pts.
Tags:
Access 2007 functions
Microsoft Access
Microsoft 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?

Answer Wiki

Thanks. We'll let you know when a new response is added.

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])

—————————–

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • AccessFlunky
    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?
    50 pointsBadges:
    report
  • carlosdl
    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.
    69,365 pointsBadges:
    report
  • AccessFlunky
    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!
    50 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following