Sql query problem

[Manesti]

Hi, I have a field in atable containing a date value (date1), I want a query that will subtract the stored date(date1) from the current date. for all the records. Please I need help. Database is Ms access. Thank you.

 

[jmcilhinney]

I believe Access supports a DateDiff function so you can just use that in your query. It works in the same way as the VB.NET DateDiff function. Access also has a Now function to get the current date and time. I'm not sure whether it also has a Today function for just the current date.

 

[Manesti]

thanks I used the datediff function. I was able to figure out how. I just called the query I created in access from my vb.net code, passing it the current date as a parameter.

SELECT STAFFS.StaffCode, STAFFS.LastName, STAFFS.FirstName, STAFFS.MiddleName, STAFFS.DepartmentName, STAFFS.SerialNumber, STAFFS.FileNumber, STAFFS.sex, STAFFS.State, STAFFS.localgovt, STAFFS.MaritalStatus, STAFFS.Rank, STAFFS.GradeLevel, STAFFS.step, STAFFS.Qualification, STAFFS.appDate, DateDiff("yyyy",[staffs.dateLagos],?) AS [Number of years], DateDiff("m",[staffs.dateLagos],?) AS [Number of Month], PROGRESSION.one, PROGRESSION.two, PROGRESSION.three, PROGRESSION.four, PROGRESSION.five, PROGRESSION.six
FROM STAFFS INNER JOIN PROGRESSION ON STAFFS.EmployeeID=PROGRESSION.EmployeeID;