I am developing a web-based Access dbase and I need to calculate the number of months between the current data and the date of employment of the user. The information will be displayed in the Acces dbase table and in a datagrid. The two fields in the dbase table are "empDate" and "time_in_Service".
I have tried using the DateDiff function but Access cannot recognize the field name ("empDate") in the function. How do I calculate the months of service as the default value in the "time_in_Service" field based on the current date and a reference to the "empDate" field?
Assuming the "time_in_Service" is supposed to be relative to the current date, how can you store it in a table? Each time you access the data, the current date will be different. The appropriate way to display this data is to retrieve the empDate, then calculate the TimeInService in your code.
Here's an example using the Northwind Employees table...
SELECT Employees.HireDate, DateDiff("m",[HireDate],Now()) AS time_in_Service